Counting consecutive days with gaps and conditions in SQL ?

We can use dateadd and - row_number to group the periods of Sick Leave. Following your comment I've added a second query to find the total number of days Sick Leave where more than one day taken together ie not counting single days.

 with distinctHours as(
select distinct * from Hours),
cte as(
select
  Agent, 
  Date, 
  Category,
  DATEADD(DD,
  -1 * row_number() over (partition by agent,Category order by date)
  ,Date)as continuous
from distinctHours)
select
  Agent,
  min(date) start_date,
  count(date) number_days,
  Category
from cte
where Category = 'SickLeave'
group by 
  Agent,
  Category,
  continuous
order by min(date)
GO 

 Agent  | start_date              | number_days | Category 
:----- | :---------------------- | ----------: | :--------
Agent1 | 2022-02-25 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-03-02 00:00:00.000 |           1 | SickLeave
Agent1 | 2022-03-15 00:00:00.000 |           1 | SickLeave
Agent1 | 2022-03-17 00:00:00.000 |           4 | SickLeave
Agent1 | 2022-04-13 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-04-18 00:00:00.000 |           5 | SickLeave
Agent1 | 2022-04-25 00:00:00.000 |           3 | SickLeave
Agent1 | 2022-04-29 00:00:00.000 |           2 | SickLeave 

 with distinctHours as(
select distinct * from Hours),
cte as(
select
  Agent, 
  Date, 
  Category,
  DATEADD(DD,
  -1 * row_number() over (partition by agent,Category order by date)
  ,Date)as continuous
from distinctHours),
preQuery as(
select
  Agent,
  min(date) start_date,
  count(date) number_days,
  Category
from cte
where Category = 'SickLeave'
group by 
  Agent,
  Category,
  continuous
)
select 
  Agent,
  sum(number_days) continuous_sick_leave,
  Category
from preQuery
where number_days > 1
group by Agent, Category;
GO 

 Agent  | continuous_sick_leave | Category 
:----- | --------------------: | :--------
Agent1 |                    20 | SickLeave 

db<>fiddle

* Be the first to Make Comment