CODEWITHSUNDEEP
sqlsql-serverselect
Lazloo Xp
Lazloo Xp

Reputation: 988

Filter for first change in SQL Server table

I facing the issue that I have a SQL Server table that saves the status of a variable V1 every day. I am only interested in the information when does the variable changes its status and what is the new status.

The original table follows the given schema:

V1 Status Date
-----------------
X  A      date_1
Y  C      date_2
X  B      date_3
X  B      date_4
Y  D      date_5
X  B      date_6
Y  D      date_7
X  A      date_8

The resulting table should like

V1 Status Date
-----------------
X  A      date_1
X  B      date_3
X  A      date_8
Y  C      date_2
Y  D      date_5

Can somebody provide a T-SQL statement that does this job?

Thanks

Upvotes: 0

Views: 55

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this. It works with sql server 2008 and above.

DECLARE @Temp TABLE (V1 VARCHAR(10), Status VARCHAR(10), Date VARCHAR(10))
INSERT INTO @Temp  VALUES
('X', 'A', 'date_1'),
('Y', 'C', 'date_2'),
('X', 'B', 'date_3'),
('X', 'B', 'date_4'),
('Y', 'D', 'date_5'),
('X', 'B', 'date_6'),
('Y', 'D', 'date_7'),
('X', 'A', 'date_8')

;WITH CTE AS (
    select *, RN = ROW_NUMBER() OVER (PARTITION BY V1 ORDER BY Date) FROM @Temp 
)
SELECT T1.V1, T1.Status, T1.Date  
FROM 
    CTE T1 
    LEFT JOIN CTE T2 ON T1.V1 = T2.V1 AND T1.RN = (T2.RN + 1 )
WHERE T2.Status IS NULL OR T2.Status <> T1.Status

Result:

V1         Status     Date
---------- ---------- ----------
X          A          date_1
X          B          date_3
X          A          date_8
Y          C          date_2
Y          D          date_5

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can easily achieve this using LAG window function:

;WITH CTE AS (
   SELECT V1, Status, [Date],       
          LAG(Status) OVER (PARTITION BY V1 ORDER BY [Date]) prev_Status
   FROM mytable
)
SELECT V1, Status, [Date]
FROM CTE
WHERE prev_Status IS NULL OR prev_Status <> Status

The CTE uses LAG to fetch the Status value of the immediately preceding row. It uses a PARTITION clause so as to perform this separately for each V1 group.

Note: LAG is only available from SQL Server 2012

Upvotes: 1

Related Questions