Home How to retrieve only the records where stat changes?
Reply: 1

How to retrieve only the records where stat changes?

Amy
1#
Amy Published in 2018-02-11 19:31:28Z

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, '2017-01-03', 100), (1, '2017-01-05', 100),
       (1, '2017-01-07', 150), (1, '2017-01-09', 150), (1, '2017-02-01', 150),
       (1, '2017-02-02', 100), (1, '2017-02-12', 100), (1, '2017-02-15', 100),
       (1, '2017-02-17', 150), (1, '2017-03-09', 150), (1, '2017-03-11', 150),
       (2, '2017-01-01', 100), (2, '2017-01-03', 100), (2, '2017-01-05', 100),
       (2, '2017-01-07', 150), (2, '2017-01-09', 150), (2, '2017-02-01', 150),
       (2, '2017-02-02', 100), (2, '2017-02-12', 100), (2, '2017-02-15', 100),
       (2, '2017-02-17', 150), (2, '2017-03-09', 150), (2, '2017-03-11', 150)

I tried to use something like this

with a as
(
    select 
        id, date,
        ROW_NUMBER() over (partition by date order by id) as rowNum  
    from 
        x
), b as 
(
     select 
         id, date,
         ROW_NUMBER() over (partition by id, stat order by date) as rowNum   
     from 
         x
)
select min(b.date)  
from a
join b on b.id = a.id
having max(a.date) > max(b.date)
tarheel
2#
tarheel Reply to 2018-02-11 22:08:37Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.369845 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO