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

How to retrieve only the records where stat changes?

user1266
1#
user1266 Published in May 21, 2018, 6:45 pm

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

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

© 2016 Powered by mzan.com design MATCHINFO