How To Retrieve Only The Records Where Stat Changes?

I want to get same output: using the following sample data create table x ( id int, date datetime, stat int ) insert into x values (1, '2017-01-01', 100), (1, '

Solution 1:

What you are looking for is a gaps-and-islands scenario, where you only have islands. In this scenario what defines the start of an island is a change in the stat value within a id, while evaluating the dataset in date order.

The lag window function is used below to compare values across rows, and see if you need to include it in the output.

select b.id
, b.stat
, b.date
from (
    select a.id
    , a.date
    , a.stat
    , case lag(a.stat,1,NULL) over (partition by a.id order by a.date asc) when a.stat then 0 else 1 end as include_flag
    from x as a
    ) as b
where b.include_flag = 1