Need help! Complex query involving two tables

[SDCSA]

Hello All,

I have two tables each with 100 fields in it. The first table is Employee and the second is Employee_Journal.

Whenever a change is made to a field in employee table, the field value is updated. A corresponding record is entered in the journal table. The journal table is like a repository for all changes made. For instance, as shown below, the SUPERVISOR_ID for EMPLID 404749 is changed three times. Therefore, for each change made, a record is entered in journal table. The Employee table shows the latest. Change can be made on any of the 100 fields on the Employee table.

EMPLOYEE_JOURNAL

EMPLID DEPT SUPERVISOR_ID ...........
404747 FIC10000
404748 FIC10000
404746 CAM10000
404745 CAM10000
404749 ACT10102 404746
404749 ACT10102 404747
404749 ACT10102 404748

EMPLOYEE

EMPLID DEPTID SUPERVISOR_ID .............
----------- ----------- ----------
404749 ACT10102 404749

There is also a date field. Now I need to write a query so that it will fetch the oldvalue and newvalue. If fields 40, 50, 60 are changed today, we need to write a query which will fetch the new values from Employee table and the old values (latest) from the Journal table. Whenever a field is changed, a record will be inserted in the Journal table. I need to do this for current date. The query should check all the employees.

I would really appreciate if you can help me regarding this.

Thanks in anticipation.

sdcsa

 

[BillDHS]

I don't see any easy solution to this question. I have the feeling these tables should have been normalized to a set of smaller tables long ago.

If you have to look at changes to any of the hundred fields, you would have a join with 100 "or"'s looking for the changed value.

I have another thought that is not much better, if you created a bit map field with 100 1's and 0's and change the bit in that position when a value change is made you could join on employee ID and get the max date based on the change bit.

 

[SDCSA]

Hi.

Thanks for the reply. What I have just discovered is that, no matter how many updates are made on any field, the last row (for the current date and for an employee) should be the one with latest old values. So, that row should be compared with the row from Employee table (on a particular date for that employee) and find out the new and old values. This needs to be done for all employees. There id a TIMESTAMP in each row that helps in finding the records with changes made on a current date and also in finding the latest update of an employee.

Can we make a query?

Thanks again and I appreciate if you can help me with a query.

 

[BillDHS]

I'm not sure I completely understand, but if you take the max_timestamp row of the journal table and UNION that with the current table, you will retrieve both rows in the result:

(Remember both results in a UNION need identical column counts and data type definitions, so be careful with select*)

SELECT * FROM Employee

UNION

SELECT * FROM Empl_jour,
(select EmpID, MAX(Timestamp)
from Empl_jour
group by EmpID) TempTab(EID, MTS)
WHERE Empl_jour.EmpID = TempTab.EID
AND Empl_jour.TimeStamp = TempTab.MTS

Order By EmpID, TimeStamp ;