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