Capturing changes from two columns - SQL Server

Question

I have following table:

day              cat    x1    x2
2019-10-25       A      P     Open
2019-10-26       A      P     Close
2019-10-27       A      P     Close
2019-10-28       A      I     Close
2019-10-26       B      P     Close
2019-10-27       B      P     Open
2019-10-28       B      P     Open

How can I get rows where either "x1" or "x2" has changed within a category "cat"?

day              cat    x1    x2
2019-10-26       A      P     Close
2019-10-28       A      I     Close
2019-10-27       B      P     Open

I have found some questions with one column changed, but struggling a bit with categorized table and capturing changes from two columns

that is the version of the SQL Server Management Studio. Not the SQL Server. Do a print @@version to see — Squirrel
– Squirrel, Commented Oct 30, 2019 at 12:05

Squirrel · Accepted Answer · 2019-10-30 12:01:39Z

2

you can use the window function lag() on column x1 and x2 and compare. What you want is a OR condition

select  *
from
(
    select  *,
            prev_x1 = lag(x1) over(partition by [cat] order by [day]),
            prev_x2 = lag(x2) over(partition by [cat] order by [day])
    from    your_table t
) d
where   x1  <> prev_x1
or      x2  <> prev_x2

answered Oct 30, 2019 at 12:01

Squirrel

24.8k5 gold badges41 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EzLo · Accepted Answer · 2019-10-30 12:06:16Z

1

If you don't have the LAG function, you can use a ROW_NUMBER() to generate a ranking of categories by day, then INNER JOIN against previous record.

;WITH RowNumberByCategory AS
(
    SELECT
        T.day,
        T.cat,
        T.x1,
        T.x2,
        RowNumberByCategory = ROW_NUMBER() OVER (PARTITION BY T.cat ORDER BY T.day ASC)
    FROM
        YourTable AS T
)
SELECT
    T.*
FROM
    RowNumberByCategory AS T
    INNER JOIN RowNumberByCategory AS N ON
        T.cat = N.cat AND
        T.RowNumberByCategory - 1 = N.RowNumberByCategory
WHERE
    T.x1 <> N.x1 OR T.x2 <> N.x2

Make sure to check against NULL values with ISNULL on the WHERE if your x1 or x2 columns can be NULL.

answered Oct 30, 2019 at 12:06

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Collectives™ on Stack Overflow

Capturing changes from two columns - SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related