Change History Within Same Table For Each Record

Dear All,

A table ‘Y’ is an audit table having records as history from the main table ‘X’, when a new record gets created in table ‘X’, an entry is saved in the audit table ‘Y’ as well. Later on, if any change occurs to any of the columns of table ‘X’, a new record is created in table ‘Y’.

I need to construct a query which can select the latest record from table ‘Y’ and reverse tracking based on timestamp if any of the changes to any column were done to a particular record I have to display the column name along with the old value and the new value. Means I have to list records in a date range which are modified as compared to the same record in the same table previously available.

Following is an example table in which there is a change in column ‘type’ for ponum = 27265 so I must display this record.


ponum username timestamp period type
27265 ACC10 19/06/2005 13:34 10 WEEKS
27265 ACC10 20/06/2005 11:27 10 DAYS
27266 ACC04 19/06/2005 13:58 10 DAYS
27269 ACC04 21/06/2005 11:24 1 WEEKS
27271 ACC04 21/06/2005 14:38 2 WEEKS

So my query should return

ponum username timestamp Column oldvalue newvalue
27265 ACC10 20/06/2005 11:27 type WEEKS DAYS

If any one can help please.

Assume the table has following rows:
ponum username timestamp period type
27265 ACC10 19/06/2005 13:34 10 WEEKS
27265 ACC10 20/06/2005 11:27 10 DAYS
27265 ACC10 22/06/2005 12:27 10 YEAR

What will be the output you want?

Tamil