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

 

[Turkbear]

Hi,
Just one observation..unless you include some form of timestamp or sequence number when inserting into the
Journal, you will not be able to determine the latest old value...

 

[SDCSA]

Hi.

Thanks for the reply. Yes you are right. There needs to be a sequence number. 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. Is the sequence # still necessary?

Can we make a query?

Thanks again.

 

[Turkbear]

HI,
The problem lies with determining what is the 'last' row..

Without some created value ( timestamp or seq #) the concept of 'last' is meaningless...

 

[SDCSA]

Hi,

Yes there is a timestamp (currentdate as TIMESTAMP) in every record. That determines which one is the last row for a particular employee id and for the currentdate. The last row has the latest old values of all fields. But we also need to see that we are querying for changes that are done today.

Thanks again Turkbear.