T-SQL 2005 selfjoin item audit table?

• Superdec

  Mr or Mrs. 500

  Points: 549

  More actions

  November 18, 2009 at 4:26 pm

  #139458

  Hi, I have Audit_Item_Table which records all changes happened in Item number.

  Table : Audit_Item_Table

  ItemNumber ItemLocation ItemPrice ItemQty AuditUser AuditDateTime AuditSequence AuditType

  AR1234 RA22 135.79 3.00 Chris 2009-10-01 18:56:30 1 Insert

  AR1234 RA22 135.79 3.00 Chris 2009-11-01 13:00:00 1 Update

  AR1234 RA22 135.79 4.00 Chris 2009-11-01 13:00:00 2 Update

  AR1234 RA22 135.79 4.00 Chris 2009-11-01 16:45:00 3 Delete

  AR1437 RA81 232.00 3.00 Mark 2009-11-06 11:45:20 1 Insert

  AR1437 RA81 232.00 3.00 Paul 2009-11-06 11:45:20 1 Update

  AR1437 RA81 135.77 3.00 Paul 2009-11-06 11:45:20 2 Update

  AR1789 RA49 265.00 12.00 Matt 2009-10-12 17:18:00 1 Insert

  AR3247 RA43 675.00 3.00 Mark 2009-11-02 09:45:44 1 Insert

  AR3247 RA43 675.00 3.00 Paul 2009-11-06 11:45:20 1 Update

  AR3247 RA43 456.32 11.00 Paul 2009-11-06 11:45:20 2 Update

  AR3247 RA43 456.32 11.00 Mark 2009-11-16 10:45:20 1 Update

  AR3247 RA43 456.32 5.00 Mark 2009-11-16 10:45:20 2 Update

  AR3247 RA43 456.32 5.00 Scott 2009-11-18 18:05:30 1 Delete

  Final Excepted Result Table :

  ItemNumber ItemLocation ItemPriceNew ItemPriceOld ItemQtyNew ItemQtyOld AuditUser AuditDateTime AuditType

  AR1234 RA22 135.79 -- 3.00 -- Chris 2009-10-01 18:56:30 Insert

  AR1234 RA22 -- -- 4.00 3.00 Chris 2009-11-01 13:00:00 Update

  AR1234 RA22 -- 135.79 -- 4.00 Chris 2009-11-01 16:45:00 Delete AR1437 RA81 232.00 -- 3.00 -- Mark 2009-11-06 11:45:20 Insert

  AR1437 RA81 135.77 232.00 -- 3.00 Paul 2009-11-06 11:45:20 Update AR1789 RA49 265.00 -- 12.00 -- Matt 2009-10-12 17:18:00 Insert AR3247 RA43 675.00 -- 3.00 -- Mark 2009-11-02 09:45:44 Insert

  AR3247 RA43 456.32 675.00 11.00 3.00 Paul 2009-11-06 11:45:20 Update

  AR3247 RA43 456.32 -- 5.00 11.00 Mark 2009-11-16 10:45:20 Update

  AR3247 RA43 -- 456.32 -- 5.00 Scott 2009-11-18 18:05:30 Delete

  Audit_Item_Table explanation: (Insert, Update, Delete)

  >> AuditType = "Insert" when item inserted and it put AuditSequence = 1

  >> AuditType = "Update" when item updated it create record with AuditSequence = 1 with old vlaue for when same record (same ItemNumber, ItemLocation, AuditUser,AuditDateTime, AuditType) and it create new record with AuditSequence = 2 with new value.

  >> AuditType = "Delete" when item deleted and it put AuditSequence = 1

  So whenever ItemNumber & ItemLocation inserted it will create one record AuditType = "Insert" and put AuditSequence = 1

  ItemNumber & ItemLocation updated it will create two records with AuditType = "Update" and put AuditSequence = 1 & 2, Where value 1 is old value and 2 is new value

  ItemNumber & ItemLocation deleted it will create one record with AuditType = "Delete"and put AuditSequence = 1.

  Pls can I have T-SQL 2005 query for my "Final Excepted Result Table"? Thank you.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 18, 2009 at 4:51 pm

  #1081299

  What have you tried so far and where you got stuck?

  Did you try to left outer join on ItemNumber, ItemLocation and AuditDateTime (maybe even AuditUser) and t1.AuditSequence = t2.AuditSequence -1 to get the prev. row? Do you have an index on the table that will support that query?

  Note: Your description does not match the sample data for column [AuditSequence] (you have a value of 3 for a DELETE AuditType, which isn't explained...)

  If you need further assistance please provide sample data in a ready to use format as described in the first link in my signature. It will help us help you.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]