CODEWITHSUNDEEP
sqlsql-serverrecord
Brandon
Brandon

Reputation: 45

SQL Server Return Rows Where Field Changed

I have a table with 3 values. 

ID     AuditDateTime       UpdateType
12     12-15-2015 18:09    1
45     12-04-2015 17:41    0
75     12-21-2015 04:26    0
12     12-17-2015 07:43    0
35     12-01-2015 05:36    1
45     12-15-2015 04:35    0

I'm trying to return only records where the UpdateType has changed from AuditDateTime based on the IDs. So in this example, ID 12 changes from the 12-15 entry to the 12-17 entry. I would want that record returned. There will be multiple instances of ID 12, and I need all records returned where an ID's UpdateType has changed from its previous entry. I tried adding a row_number but it didn't insert sequentially because the records are not in the table in order. I've done a ton of searching with no luck. Any help would be greatly appreciated.

Upvotes: 3

Views: 78

Answers (3)

Steven
Steven

Reputation: 911

By using a CTE it is possible to find the previous record based upon the order of the AuditDateTime

WITH CTEData AS 
(SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AuditDateTime) [ROWNUM], *
FROM @tmpTable)
SELECT A.ID, A.AuditDateTime, A.UpdateType 
FROM CTEData A INNER JOIN CTEData B 
        ON  (A.ROWNUM - 1) = B.ROWNUM AND 
            A.ID = B.ID 
WHERE A.UpdateType <> B.UpdateType

The Inner Join back onto the CTE will give in one query both the current record (Table Alias A) and previous row (Table Alias B).

Upvotes: 3

Andomar
Andomar

Reputation: 238048

You can use the lag() window function to find the previous value for the same ID. Now you can pick only those rows that introduce a change:

select  *
from    (
        select  lag(UpdateType) over (
                  partition by ID
                  order by AuditDateTime) as prev_updatetype
        ,       *
        from    YourTable
        ) sub
where   prev_updatetype <> updatetype

Example at SQL Fiddle.

Upvotes: 0

Tom H
Tom H

Reputation: 47444

This should do what you're trying to do I believe

SELECT
    T1.ID,
    T1.AuditDateTime,
    T1.UpdateType
FROM
    dbo.My_Table T1
INNER JOIN dbo.My_Table T2 ON
    T2.ID = T1.ID AND
    T2.UpdateType <> T1.UpdateType AND
    T2.AuditDateTime < T1.AuditDateTime
LEFT OUTER JOIN dbo.My_Table T3 ON
    T3.ID = T1.ID AND
    T3.AuditDateTime < T1.AuditDateTime AND
    T3.AuditDateTime > T2.AuditDateTime
WHERE
    T3.ID IS NULL

Alternatively:

SELECT
    T1.ID,
    T1.AuditDateTime,
    T1.UpdateType
FROM
    dbo.My_Table T1
INNER JOIN dbo.My_Table T2 ON
    T2.ID = T1.ID AND
    T2.UpdateType <> T1.UpdateType AND
    T2.AuditDateTime < T1.AuditDateTime
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            dbo.My_Table T3
        WHERE
            T3.ID = T1.ID AND
            T3.AuditDateTime < T1.AuditDateTime AND
            T3.AuditDateTime > T2.AuditDateTime
    )

The basic gist of both queries is that you're looking for rows where an earlier row had a different type and no other rows exist between the two rows (hence, they're sequential). Both queries are logically identical, but might have differing performance.

Also, these queries assume that no two rows will have identical audit times. If that's not the case then you'll need to define what you expect to get when that happens.

Upvotes: 0

Related Questions