T-SQL Determine Status Changes in History Table

Question

I have an application which logs changes to records in the "production" table to a "history" table. The history table is basically a field for field copy of the production table, with a few extra columns like last modified date, last modified by user, etc.

This works well because we get a snapshot of the record anytime the record changes. However, it makes it hard to determine unique status changes to a record. An example is below.

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 above table (data comes from the real system with other fields removed for brevity and security) BoxID 1 has had 9 changes to the production record. Some of those updates resulted in statuses being changed and some did not, which means other fields (those not shown) have changed.

I need to be able, in TSQL, to extract from this data the unique status changes. The output I am 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

This is not as easy as grouping by StatusID and SubStatusID and taking the min(ModifiedTime) then joining back into the history table since statuses can go backwards as well (see StatusID 4, SubStatusID 11 gets set twice).

Any help would be greatly appreciated!

Answer 1

Does this do work 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
;