CODEWITHSUNDEEP
sqlsql-server
g.hu
g.hu

Reputation: 1

SQL query to find max, min when data changed

need sql select min(date),max(date)when change stat for each code iam try this query it work well with where condition but he give me wrong data without condition

select x.code,x.stat,x.grp, min(date) as min_date, max(date) as max_date
from (
    select code,stat,date ,
          row_number() over (order by date)- row_number() over (partition by stat order by date) as grp
    from  c 
     where code='1')  X
   group by code,stat,grp
   order by min(date)

my table

code     stat     date
1         s       01/01/2012
1         s       02/01/2012
1         i       03/01/2012
1         s       04/01/2012
2         a       01/01/2012
2         a       02/01/2012

need this result

code     stat     min date     max date
1         s       01/01/2012  02/01/2012
1         i       03/01/2012  03/01/2012
1         s       04/01/2012  04/01/2012
2         a       01/01/2012  02/01/2012

Upvotes: 0

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

I think your code is basically correct except for the missing partition by:

select c.code, c.stat, c.grp,
       min(c.date) as min_date, max(c.date) as max_date
from (select c.*
             row_number() over (partition by c.code order by c.date) as seqnum,
             row_number() over (partition by c.code, c.stat order by date) as seqnum_d
      from c 
     ) c
group by c.code, c.stat, c.grp
order by min(c.date);

Upvotes: 1

Related Questions