Geek Answers Handbook

T-SQL Defining State Changes in a History Table

I have an application that writes changes to records in the "production" table in the "history" table. A history table is basically a field for a copy of a field in a production table with several additional columns, such as the last modified date, the last modified by the user, etc.

This works well because we get a snapshot of the recording anytime the recording changes. However, this makes it difficult to identify unique status changes for the record. The following is an example.

BoxID StatusID SubStatusID ModifiedTime 1 4 27 2011-08-11 15:31 1 4 11 2011-08-11 15:28 1 4 11 2011-08-10 09:07 1 5 14 2011-08-09 08:53 1 5 14 2011-08-09 08:19 1 4 11 2011-08-08 14:15 1 4 9 2011-07-27 15:52 1 4 9 2011-07-27 15:49 1 2 8 2011-07-26 12:00

As you can see in the table above (data comes from a real system with the removal of other fields for brevity and security) BoxID 1 has 9 changes in the production record. Some of these updates have changed statuses, and some have not, which means that other fields (those that are not shown) have changed.

I need to get unique status from this data in TSQL. The result I was looking for given the above input table is below.

 BoxID StatusID SubStatusID ModifiedTime 1 4 27 2011-08-11 15:31 1 4 11 2011-08-10 09:07 1 5 14 2011-08-09 08:19 1 4 11 2011-08-08 14:15 1 4 9 2011-07-27 15:49 1 2 8 2011-07-26 12:00

It is not so simple as grouping by StatusID and SubStatusID, and then min(ModifiedTime) join the history table, since statuses can also go back (see StatusID 4, SubStatusID 11 is set twice).

Any help would be greatly appreciated!

+4
source share
2 answers

This works for you.

 ;WITH Boxes_CTE AS ( SELECT Boxid, StatusID, SubStatusID, ModifiedTime, ROW_NUMBER() OVER (PARTITION BY Boxid ORDER BY ModifiedTime) AS SEQUENCE FROM Boxes ) SELECT b1.Boxid, b1.StatusID, b1.SubStatusID, b1.ModifiedTime FROM Boxes_CTE b1 LEFT OUTER JOIN Boxes_CTE b2 ON b1.Boxid = b2.Boxid AND b1.Sequence = b2.Sequence + 1 WHERE b1.StatusID <> b2.StatusID OR b1.SubStatusID <> b2.SubStatusID OR b2.StatusID IS NULL ORDER BY b1.ModifiedTime DESC ;
+5
source 
 Select BoxID,StatusID,SubStatusID FROM Staty CurrentStaty INNER JOIN ON ( Select BoxID,StatusID,SubStatusID FROM Staty PriorStaty ) Where Staty.ModifiedTime= (Select Max(PriorStaty.ModifiedTime) FROM PriorStaty Where PriortStaty.ModifiedTime<Staty.ModifiedTime) AND Staty.BoxID=PriorStaty.BoxID AND NOT ( Staty.StatusID=PriorStaty.StatusID AND Staty.SubStatusID=PriorStaty.StatusID )
+2
source

More articles:


All Articles