none
T-SQL 2005 join same Audit table? RRS feed

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