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
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

 

[sem]

Your task is a bit ambiguous, because a number of updates may be done during 1 second, thus you can not be sure which one is the last or is the first.

Regards, Dima