SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Hours(
Agent VARCHAR(50),
Date DATETIME,
Category VARCHAR(50));

INSERT INTO Hours (Agent, Date, Category)
VALUES 
('Agent1', '2022-02-25 00:00:00', 'SickLeave'),
('Agent1', '2022-02-26 00:00:00', 'SickLeave'),
('Agent1', '2022-02-27 00:00:00', 'SickLeave'),
('Agent1', '2022-03-02 00:00:00', 'SickLeave'),
('Agent1', '2022-03-03 00:00:00', 'Work'),
('Agent1', '2022-03-03 00:00:00', 'Work'),
('Agent1', '2022-03-03 00:00:00', 'Work'),
('Agent1', '2022-03-04 00:00:00', 'Work'),
('Agent1', '2022-03-04 00:00:00', 'Work'),
('Agent1', '2022-03-06 00:00:00', 'Work'),
('Agent1', '2022-03-07 00:00:00', 'Work'),
('Agent1', '2022-03-07 00:00:00', 'Work'),
('Agent1', '2022-03-07 00:00:00', 'Work'),
('Agent1', '2022-03-08 00:00:00', 'Work'),
('Agent1', '2022-03-08 00:00:00', 'Work'),
('Agent1', '2022-03-08 00:00:00', 'Work'),
('Agent1', '2022-03-09 00:00:00', 'Other absence'),
('Agent1', '2022-03-10 00:00:00', 'Other absence'),
('Agent1', '2022-03-14 00:00:00', 'Other absence'),
('Agent1', '2022-03-14 00:00:00', 'Work'),
('Agent1', '2022-03-14 00:00:00', 'Work'),
('Agent1', '2022-03-15 00:00:00', 'SickLeave'),
('Agent1', '2022-03-17 00:00:00', 'SickLeave'),
('Agent1', '2022-03-18 00:00:00', 'SickLeave'),
('Agent1', '2022-03-19 00:00:00', 'SickLeave'),
('Agent1', '2022-03-20 00:00:00', 'SickLeave'),
('Agent1', '2022-03-21 00:00:00', 'Work'),
('Agent1', '2022-03-21 00:00:00', 'Work'),
('Agent1', '2022-03-21 00:00:00', 'Work'),
('Agent1', '2022-03-21 00:00:00', 'Work'),
('Agent1', '2022-03-23 00:00:00', 'Work'),
('Agent1', '2022-03-23 00:00:00', 'Work'),
('Agent1', '2022-03-23 00:00:00', 'Work'),
('Agent1', '2022-03-24 00:00:00', 'Work'),
('Agent1', '2022-03-24 00:00:00', 'Work'),
('Agent1', '2022-03-24 00:00:00', 'Work'),
('Agent1', '2022-03-25 00:00:00', 'Work'),
('Agent1', '2022-03-25 00:00:00', 'Work'),
('Agent1', '2022-03-25 00:00:00', 'Work'),
('Agent1', '2022-03-25 00:00:00', 'Other absence'),
('Agent1', '2022-03-25 00:00:00', 'Work'),
('Agent1', '2022-03-28 00:00:00', 'Work'),
('Agent1', '2022-03-28 00:00:00', 'Work'),
('Agent1', '2022-03-28 00:00:00', 'Work'),
('Agent1', '2022-03-29 00:00:00', 'Work'),
('Agent1', '2022-03-29 00:00:00', 'Work'),
('Agent1', '2022-03-29 00:00:00', 'Work'),
('Agent1', '2022-03-30 00:00:00', 'Work'),
('Agent1', '2022-03-30 00:00:00', 'Work'),
('Agent1', '2022-03-30 00:00:00', 'Work'),
('Agent1', '2022-03-31 00:00:00', 'Work'),
('Agent1', '2022-03-31 00:00:00', 'Work'),
('Agent1', '2022-03-31 00:00:00', 'Work'),
('Agent1', '2022-03-31 00:00:00', 'Work'),
('Agent1', '2022-04-01 00:00:00', 'Work'),
('Agent1', '2022-04-01 00:00:00', 'Work'),
('Agent1', '2022-04-01 00:00:00', 'Work'),
('Agent1', '2022-04-04 00:00:00', 'Work'),
('Agent1', '2022-04-04 00:00:00', 'Work'),
('Agent1', '2022-04-04 00:00:00', 'Work'),
('Agent1', '2022-04-05 00:00:00', 'Work'),
('Agent1', '2022-04-05 00:00:00', 'Work'),
('Agent1', '2022-04-05 00:00:00', 'Work'),
('Agent1', '2022-04-06 00:00:00', 'Work'),
('Agent1', '2022-04-06 00:00:00', 'Work'),
('Agent1', '2022-04-06 00:00:00', 'Work'),
('Agent1', '2022-04-08 00:00:00', 'Work'),
('Agent1', '2022-04-08 00:00:00', 'Work'),
('Agent1', '2022-04-08 00:00:00', 'Work'),
('Agent1', '2022-04-09 00:00:00', 'Work'),
('Agent1', '2022-04-09 00:00:00', 'Work'),
('Agent1', '2022-04-09 00:00:00', 'Work'),
('Agent1', '2022-04-10 00:00:00', 'Work'),
('Agent1', '2022-04-11 00:00:00', 'Work'),
('Agent1', '2022-04-11 00:00:00', 'Work'),
('Agent1', '2022-04-11 00:00:00', 'Other absence'),
('Agent1', '2022-04-13 00:00:00', 'Work'),
('Agent1', '2022-04-13 00:00:00', 'Work'),
('Agent1', '2022-04-13 00:00:00', 'Work'),
('Agent1', '2022-04-13 00:00:00', 'SickLeave'),
('Agent1', '2022-04-14 00:00:00', 'SickLeave'),
('Agent1', '2022-04-14 00:00:00', 'Work'),
('Agent1', '2022-04-15 00:00:00', 'Holiday'),
('Agent1', '2022-04-15 00:00:00', 'SickLeave'),
('Agent1', '2022-04-18 00:00:00', 'SickLeave'),
('Agent1', '2022-04-18 00:00:00', 'Work'),
('Agent1', '2022-04-19 00:00:00', 'Work'),
('Agent1', '2022-04-19 00:00:00', 'Work'),
('Agent1', '2022-04-19 00:00:00', 'Work'),
('Agent1', '2022-04-19 00:00:00', 'SickLeave'),
('Agent1', '2022-04-20 00:00:00', 'SickLeave'),
('Agent1', '2022-04-20 00:00:00', 'Work'),
('Agent1', '2022-04-20 00:00:00', 'Work'),
('Agent1', '2022-04-20 00:00:00', 'Work'),
('Agent1', '2022-04-21 00:00:00', 'Work'),
('Agent1', '2022-04-21 00:00:00', 'Work'),
('Agent1', '2022-04-21 00:00:00', 'SickLeave'),
('Agent1', '2022-04-21 00:00:00', 'Work'),
('Agent1', '2022-04-22 00:00:00', 'Work'),
('Agent1', '2022-04-22 00:00:00', 'Work'),
('Agent1', '2022-04-22 00:00:00', 'SickLeave'),
('Agent1', '2022-04-25 00:00:00', 'Work'),
('Agent1', '2022-04-25 00:00:00', 'SickLeave'),
('Agent1', '2022-04-25 00:00:00', 'Work'),
('Agent1', '2022-04-26 00:00:00', 'SickLeave'),
('Agent1', '2022-04-26 00:00:00', 'Work'),
('Agent1', '2022-04-26 00:00:00', 'Work'),
('Agent1', '2022-04-27 00:00:00', 'Work'),
('Agent1', '2022-04-27 00:00:00', 'Work'),
('Agent1', '2022-04-27 00:00:00', 'SickLeave'),
('Agent1', '2022-04-27 00:00:00', 'Work'),
('Agent1', '2022-04-29 00:00:00', 'Work'),
('Agent1', '2022-04-29 00:00:00', 'SickLeave'),
('Agent1', '2022-04-29 00:00:00', 'Work'),
('Agent1', '2022-04-30 00:00:00', 'SickLeave'),
('Agent1', '2022-04-30 00:00:00', 'Work'),
('Agent1', '2022-04-30 00:00:00', 'Work'),
('Agent1', '2022-04-30 00:00:00', 'Work');
118 rows affected

select distinct 
  * 
from Hours 
where Category = 'SickLeave'
order by Agent, Date;


Agent
Date
Category


Agent1
2022-02-25 00:00:00.000
SickLeave

Agent1
2022-02-26 00:00:00.000
SickLeave

Agent1
2022-02-27 00:00:00.000
SickLeave

Agent1
2022-03-02 00:00:00.000
SickLeave

Agent1
2022-03-15 00:00:00.000
SickLeave

Agent1
2022-03-17 00:00:00.000
SickLeave

Agent1
2022-03-18 00:00:00.000
SickLeave

Agent1
2022-03-19 00:00:00.000
SickLeave

Agent1
2022-03-20 00:00:00.000
SickLeave

Agent1
2022-04-13 00:00:00.000
SickLeave

Agent1
2022-04-14 00:00:00.000
SickLeave

Agent1
2022-04-15 00:00:00.000
SickLeave

Agent1
2022-04-18 00:00:00.000
SickLeave

Agent1
2022-04-19 00:00:00.000
SickLeave

Agent1
2022-04-20 00:00:00.000
SickLeave

Agent1
2022-04-21 00:00:00.000
SickLeave

Agent1
2022-04-22 00:00:00.000
SickLeave

Agent1
2022-04-25 00:00:00.000
SickLeave

Agent1
2022-04-26 00:00:00.000
SickLeave

Agent1
2022-04-27 00:00:00.000
SickLeave

Agent1
2022-04-29 00:00:00.000
SickLeave

Agent1
2022-04-30 00:00:00.000
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)
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)


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;


Agent
continuous_sick_leave
Category


Agent1
22
SickLeave


select 
  Agent,
  count(distinct Date) Days_Sick_Leave
from Hours 
where Agent = 'Agent1' and Category = 'SickLeave'
group by Agent;


Agent
Days_Sick_Leave


Agent1
22

Agent	Date	Category
Agent1	2022-02-25 00:00:00.000	SickLeave
Agent1	2022-02-26 00:00:00.000	SickLeave
Agent1	2022-02-27 00:00:00.000	SickLeave
Agent1	2022-03-02 00:00:00.000	SickLeave
Agent1	2022-03-15 00:00:00.000	SickLeave
Agent1	2022-03-17 00:00:00.000	SickLeave
Agent1	2022-03-18 00:00:00.000	SickLeave
Agent1	2022-03-19 00:00:00.000	SickLeave
Agent1	2022-03-20 00:00:00.000	SickLeave
Agent1	2022-04-13 00:00:00.000	SickLeave
Agent1	2022-04-14 00:00:00.000	SickLeave
Agent1	2022-04-15 00:00:00.000	SickLeave
Agent1	2022-04-18 00:00:00.000	SickLeave
Agent1	2022-04-19 00:00:00.000	SickLeave
Agent1	2022-04-20 00:00:00.000	SickLeave
Agent1	2022-04-21 00:00:00.000	SickLeave
Agent1	2022-04-22 00:00:00.000	SickLeave
Agent1	2022-04-25 00:00:00.000	SickLeave
Agent1	2022-04-26 00:00:00.000	SickLeave
Agent1	2022-04-27 00:00:00.000	SickLeave
Agent1	2022-04-29 00:00:00.000	SickLeave
Agent1	2022-04-30 00:00:00.000	SickLeave