How to get date counts while considering n consecutive date ranges as single count in SQL?-CodePudding

For example there is some table with dates:

I have to write SQL query, which will return count of dates between date ranges and consider consecutive dates as a single count. So the result will be like:

consider single count if 2 consecutive dates

2022-01-01 1
2022-01-02 1
2022-01-03 2
2022-01-06 3
2022-01-07 3
2022-01-11 4

consider single count if 3 consecutive dates

2022-01-01 1
2022-01-02 1
2022-01-03 1
2022-01-06 2
2022-01-07 3
2022-01-11 4

consider single count if 4 consecutive dates

2022-01-01 1
2022-01-02 2
2022-01-03 3
2022-01-06 4
2022-01-07 4
2022-01-08 4
2022-01-09 4
2022-01-10 5
2022-01-13 6

consider single count if n consecutive dates n is configurable

CodePudding user response：

The solution first identify group of consecutive dates. And then break it into the required "n" consecutive dates

The description are in the comments. It is break up into few cte so it is easier to examine the value at each stage.

declare @n int = 3;

with cte as
(
  -- find when the date is not consecutive 
  -- using lag() to compare with current row
  select [date], 
         g = case when dateadd(day, -1, [date])
                  <>   lag([date]) over (order by [date])
                  then 1
                  else 0
                  end
  from   dates
),
cte2 as
(
  -- group conecusive dates together 
  -- by sum up g
  select *, grp = sum(g) over (order by [date])
  from   cte
),
cte3 as
(
  -- break the group by @n
  select *, rn = (row_number() over (partition by grp order by [date]) - 1) / @n
  from   cte2
)
-- dense_rank() to number it
select *, [count] = dense_rank() over (order by grp, rn)
from   cte3

db<>fiddle demo

EDIT: misunderstood your requirement. This should gives you the required result

with cte as
(
  -- find when the date is not consecutive  
  -- using lag() to compare with current row
  select [date], 
         g = case when dateadd(day, -1, [date])
                  <>   lag([date]) over (order by [date])
                  then 1
                  else 0
                  end
  from   dates
),
cte2 as
(
  -- group consecutive dates together 
  -- by sum up g
  select *, grp = sum(g) over (order by [date])
  from   cte
),
cte3 as
(
  -- count number of consecutive dates in a group
  select *, 
         c = count(*) over (partition by grp)
  from   cte2
),
cte4 as
(
  select *,
         rn = case when c >= @n
                   then (row_number() over (partition by grp order by [date]) - 1) / @n
                   else row_number() over (partition by grp order by [date])
                   end
  from   cte3
)
select *, [count] = dense_rank() over (order by grp, rn)
from   cte4

db<>fiddle demo