# T-SQL 2005 join same Audit table?

• ### Question

• Hi I have below table (SQL 2005) – CustomerAudit. If end user change anything in customer it will create a record in CustomerAudit table with AuditSequence (incremental). Let’s say XYZ Corporation with AuditSequence=1 was first record created and AuditSequence=5 is the latest record. I want “Final Result Table shows old and new value (AuditDateTime is always greater than last AuditDateTime in AuditSequence). Any idea Thanks.
TABLE : CustomerAudit

 CustomerName CustomerAddress CustomerDepot CustomerPrice CustomerDiscountGroup AuditSequence AuditUser AuditDateTime XYZ Corporation 118 High Road, Australia Australia-04 188.56 845 5 USER5 2009-10-04 09:55:00 XYZ Corporation 118 High Road, Australia Australia-04 122.00 599 4 USER10 2009-10-03 11:18:00 XYZ Corporation 118 High Road Australia-04 122.00 599 3 USER8 2009-10-02 22:18:00 XYZ Corporation 118 High Road Australia-05 122.00 845 2 USER1 2009-10-02 18:48:24 XYZ Corporation 118 High Road Australia-04 122.00 845 1 USER3 2009-10-01 12:18:00
Final Result Table :
 CustomerAddress CustomerDepot CustomerPrice CustomerDescountGroup CustomerName New Old New Old New Old New Old AuditUser AuditDateTime XYZ Corporation 118 High Road, Australia 118 High Road Australia-04 Australia-05 188.56 122.00 845 599 USER5 2009-10-04 09:55:00
Monday, November 2, 2009 4:43 AM

### All replies

• Hi Superdec,

Your example data isn't quite clear - what's your definition of the "New" and "Old" values for CustomerDepot, etc?  Is it:

1) New = Most Recent row (ie, AuditSequence 5), Old = Second Most Recent Row (AuditSequence 4)
2) New = Most Recent row (ie, AuditSequqnce 5), Old = Most recent value of CustomerDepot that was different (ie, AuditSequence 2)
3) New = Most Recent row (Ie AuditSequence 5), Old = First row (ie AuditSequence 1)

It looks like it's #2, but I can't imagine why you'd want to report on multiple changes of the old value.

Is the AuditSequence unique for the entire CustomerAudit table (e.g. as an identity value)?
Monday, November 2, 2009 5:02 AM
• what is your rules ?

What do you want to show if you have value  changed from A to B then to C
KH Tan
Monday, November 2, 2009 5:05 AM