Sql-server – GROUP BY with date ranges when values change but then change back

gaps-and-islandsquerysql servert-sql

Consider a historical table where a value was changed and then changed back:

value | date
  3   | 1/1/2019
  3   | 1/2/2019
  3   | 1/3/2019
  4   | 1/4/2019
  4   | 1/5/2019
  3   | 1/6/2019
  3   | 1/7/2019
  3   | 1/8/2019
  3   | 1/9/2019
  2   | 1/10/2019
  2   | 1/11/2019
  3   | 1/12/2019
  3   | 1/13/2019
  3   | 1/14/2019

How do I write a query that shows min and max dates that still shows the value change? I'm looking for:

value | min(date) | max(date)  | days_at_this_val
  3   | 1/1/2019  | 1/3/2019   |        3
  4   | 1/4/2019  | 1/5/2019   |        2
  3   | 1/6/2019  | 1/9/2019   |        4
  2   | 1/10/2019 | 1/11/2019  |        2
  3   | 1/12/2019 | 1/14/2019  |        3

GROUP BY obviously lumps all the 3s together, and RANK and ROW_NUMBER resume counting 3s where they left off. What's the best way to do this?

Best Answer

A common way of doing this is to compare the total order (regarding date) vs the relative order within each value, let's call the difference grp. If the grp changes, it means that date from another partition of value interfered with the current group. So by picking the min date for each value, grp we can achieve what you want. I'll leave days_at_this_val as an exercise for the reader;-)

select v, min(d) as mind, max(d) as maxd
from (
    select v, d
         , row_number() over (order by d) 
         - row_number() over (partition by v order by d) as grp
    from t
) as tmp
group by v, grp
order by min(d);

DB<>fiddle

For future postings, you will get more (and thereby better) answers if you simplify for those trying to answer, by posting ddl and sql instead of ascii tables:

create table t (v int not null, d date not null);

insert into t (v,d) values ( 3   , '1/1/2019'),
                           (3   , '1/2/2019'),
                           ...
                           (3   , '1/14/2019');

or by creating a fiddle like the one I provided in the link.