CODEWITHSUNDEEP
sql-server
xyz
xyz

Reputation: 782

Find out the periods when at least 3 consecutive days of occurrence (of values)

I have to find out the periods when at least 3 consecutive days of occurrence of values.

The table looks like this:

Dates           val
--------------------
2002-10-25 0:00 37.8
2002-11-03 0:00 38.4
2002-11-04 0:00 37.2
2002-11-05 0:00 39.1
2002-11-15 0:00 38.6
2002-11-16 0:00 39
2002-11-18 0:00 38.3
2002-11-19 0:00 38.7
2002-11-20 0:00 39.7
2002-11-21 0:00 39.2
2002-11-22 0:00 39.6
2002-11-23 0:00 38.5
2002-11-28 0:00 37.5
2003-10-03 0:00 37.1
2003-10-06 0:00 38.1
2003-10-07 0:00 37.6

The expected output is

Daterange                            NoOfDays
---------------------------------------------
2002-11-03 0:00-2002-11-05 0:00          3
2002-11-18 0:00-2002-11-23 0:00          6

Upvotes: 1

Views: 213

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can do this calculation by assigning a grouping id to the dates. A simple method is to subtract a sequence of numbers -- the difference is constant for a sequence of days. Then aggregate by this number:

select min(daterange), max(daterange), count(*) as numdays
from (select t.*,
             dateadd(day,
                     - row_number() over (order by daterange),
                     daterange) as grp
      from t
     ) t
group by grp
having count(*) >= 3;

Upvotes: 3

Related Questions