Selecting rows when there is a change in the value of column from the previous row

Question

I have the following table:-

Name   Status  Timestamp
Ben      1     2015-01-01
Ben      1     2015-01-02
Joe      1     2015-11-12   
Joe      2     2015-11-13
Joe      2     2016-12-14
Joe      2     2016-12-15
Paul     1     2015-08-16
Paul     1     2015-08-17
Paul     3     2015-08-18
Paul     3     2015-08-19
Mark     2     2015-09-20
Mark     2     2015-09-25
Mark     2     2015-09-26
Mark     3     2015-10-27

I need a query that returns only the rows where there is a change in the 'Status'. It should return the row when the 'Status' is changed and also the previous row. For instance the result should be like the below:-

Name   Status  Timestamp
Joe      1     2015-11-12   
Joe      2     2015-11-13
Paul     1     2015-08-17
Paul     3     2015-08-18
Mark     2     2015-09-26
Mark     3     2015-10-27

How can I achieve this result.

Answer 1

You can use a CTE with a CASE and LAG and LEAD to calculate what rows to select. this will work for versions 2012 and higher:

Create and populate sample table (Please save us this step in your future questions)

DECLARE @T as TABLE
(
    Name varchar(4),
    [Status] int,
    [Timestamp] date
)

INSERT INTO @T VALUES
('Joe', 1, '2015-11-12'),   
('Joe', 2, '2015-11-13'),
('Joe', 2, '2016-12-14'),
('Joe', 2, '2016-12-15'),
('Paul' ,1, '2015-08-16'),
('Paul' ,1, '2015-08-17'),
('Paul' ,3, '2015-08-18'),
('Paul' ,3, '2015-08-19'),
('Mark' ,2, '2015-09-20'),
('Mark' ,2, '2015-09-25'),
('Mark' ,2, '2015-09-26'),
('Mark' ,3, '2015-10-27')

The cte - Note that I use both lag and lead inside the case expression.

;WITH CTE AS
(
    SELECT  Name,
            [Status],
            [Timestamp],
            CASE WHEN LAG([Status]) OVER(PARTITION BY Name ORDER BY [Timestamp]) <> [Status] OR
                      LEAD([Status]) OVER(PARTITION BY Name ORDER BY [Timestamp]) <> [Status] THEN 
                1
            END As Filter
    FROM @T
)

The query:

SELECT  Name,
        [Status],
        [Timestamp]
FROM CTE
WHERE Filter = 1

Results:

Name    Status  Timestamp
Joe     1       12.11.2015 00:00:00
Joe     2       13.11.2015 00:00:00
Mark    2       26.09.2015 00:00:00
Mark    3       27.10.2015 00:00:00
Paul    1       17.08.2015 00:00:00
Paul    3       18.08.2015 00:00:00

See a live demo on rextester