SQL - Counting consecutive days with gaps and conditions-CodePudding

I'm trying to get a count of consecutive days of sick leave on employees and have gotten some of the way there, but I'm having trouble with a final puzzle.

The code I have seems to work fine if an employee is on sick leave 100%, but fails in cases of partial sick leave.

Anyone have any tips?

The code I have so far:

;With CTE
AS
(
SELECT Agent,
    CONVERT(Datetime, CONVERT(CHAR(10), Date, 104)   ' 00:00:00' ,104) as Date,
    Category,
    (CASE WHEN Category LIKE '%SickLeave%'  THEN 'Y' ELSE 'N' END) AS SickLeaveFlg,
    ROW_NUMBER() OVER (PARTITION BY Agent, (CASE WHEN Category LIKE '%SickLeave%' THEN 'Y' ELSE 'N' END) ORDER BY CONVERT(Datetime, CONVERT(CHAR(10), Date, 104)   ' 00:00:00' ,104)) AS DateSeq,
    ROW_NUMBER() OVER (PARTITION BY Agent ORDER BY CONVERT(Datetime, CONVERT(CHAR(10), Date, 104)   ' 00:00:00' ,104))  AS AgentSeq
FROM Table1
WHERE Agent=’Agent1’ AND Date BETWEEN CONVERT(Datetime, '01.02.2022 00:00:00', 104) AND CONVERT(Datetime, '30.04.2022 23:59:59', 104)
)
SELECT Agent,
    Date,
    Category,
    ResultSeq,
    ResultSeq2
FROM
(
SELECT Agent,
    Date,
    Category,
    (CASE WHEN SickLeaveFlg = 'Y' THEN ROW_NUMBER() OVER (PARTITION BY Agent, SickLeaveFlg, AgentSeq - DateSeq ORDER BY Date) ELSE 0 END) AS ResultSeq,
    (CASE WHEN SickLeaveFlg = 'Y' THEN ROW_NUMBER() OVER (PARTITION BY Agent, SickLeaveFlg, DateSeq - Date ORDER BY Date) ELSE 0 END) AS ResultSeq2
FROM CTE
) AS dt
ORDER BY Date

ResultSeq seems to produce the right result even with gaps between dates, but seems to me to fail if a date contains both work and sick leave. ResultSeq2 seems to work even if dates contains both work and sick leave, but not with gaps between dates.

And I can't figure out a way get both solutions in one count.

This is the result I get: Current results

And this is what I'm aiming for:

Desired result

An approximation of my table:

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

INSERT INTO Hours (Agent, Date, Category)
VALUES ('Agent1', '25.02.2022 00:00:00', 'SickLeave'),
('Agent1', '26.02.2022 00:00:00', 'SickLeave'),
('Agent1', '27.02.2022 00:00:00', 'SickLeave'),
('Agent1', '02.03.2022 00:00:00', 'SickLeave'),
('Agent1', '03.03.2022 00:00:00', 'Work'),
('Agent1', '03.03.2022 00:00:00', 'Work'),
('Agent1', '03.03.2022 00:00:00', 'Work'),
('Agent1', '04.03.2022 00:00:00', 'Work'),
('Agent1', '04.03.2022 00:00:00', 'Work'),
('Agent1', '06.03.2022 00:00:00', 'Work'),
('Agent1', '07.03.2022 00:00:00', 'Work'),
('Agent1', '07.03.2022 00:00:00', 'Work'),
('Agent1', '07.03.2022 00:00:00', 'Work'),
('Agent1', '08.03.2022 00:00:00', 'Work'),
('Agent1', '08.03.2022 00:00:00', 'Work'),
('Agent1', '08.03.2022 00:00:00', 'Work'),
('Agent1', '09.03.2022 00:00:00', 'Other absence'),
('Agent1', '10.03.2022 00:00:00', 'Other absence'),
('Agent1', '14.03.2022 00:00:00', 'Other absence'),
('Agent1', '14.03.2022 00:00:00', 'Work'),
('Agent1', '14.03.2022 00:00:00', 'Work'),
('Agent1', '15.03.2022 00:00:00', 'SickLeave'),
('Agent1', '17.03.2022 00:00:00', 'SickLeave'),
('Agent1', '18.03.2022 00:00:00', 'SickLeave'),
('Agent1', '19.03.2022 00:00:00', 'SickLeave'),
('Agent1', '20.03.2022 00:00:00', 'SickLeave'),
('Agent1', '21.03.2022 00:00:00', 'Work'),
('Agent1', '21.03.2022 00:00:00', 'Work'),
('Agent1', '21.03.2022 00:00:00', 'Work'),
('Agent1', '21.03.2022 00:00:00', 'Work'),
('Agent1', '23.03.2022 00:00:00', 'Work'),
('Agent1', '23.03.2022 00:00:00', 'Work'),
('Agent1', '23.03.2022 00:00:00', 'Work'),
('Agent1', '24.03.2022 00:00:00', 'Work'),
('Agent1', '24.03.2022 00:00:00', 'Work'),
('Agent1', '24.03.2022 00:00:00', 'Work'),
('Agent1', '25.03.2022 00:00:00', 'Work'),
('Agent1', '25.03.2022 00:00:00', 'Work'),
('Agent1', '25.03.2022 00:00:00', 'Work'),
('Agent1', '25.03.2022 00:00:00', 'Other absence'),
('Agent1', '25.03.2022 00:00:00', 'Work'),
('Agent1', '28.03.2022 00:00:00', 'Work'),
('Agent1', '28.03.2022 00:00:00', 'Work'),
('Agent1', '28.03.2022 00:00:00', 'Work'),
('Agent1', '29.03.2022 00:00:00', 'Work'),
('Agent1', '29.03.2022 00:00:00', 'Work'),
('Agent1', '29.03.2022 00:00:00', 'Work'),
('Agent1', '30.03.2022 00:00:00', 'Work'),
('Agent1', '30.03.2022 00:00:00', 'Work'),
('Agent1', '30.03.2022 00:00:00', 'Work'),
('Agent1', '31.03.2022 00:00:00', 'Work'),
('Agent1', '31.03.2022 00:00:00', 'Work'),
('Agent1', '31.03.2022 00:00:00', 'Work'),
('Agent1', '31.03.2022 00:00:00', 'Work'),
('Agent1', '01.04.2022 00:00:00', 'Work'),
('Agent1', '01.04.2022 00:00:00', 'Work'),
('Agent1', '01.04.2022 00:00:00', 'Work'),
('Agent1', '04.04.2022 00:00:00', 'Work'),
('Agent1', '04.04.2022 00:00:00', 'Work'),
('Agent1', '04.04.2022 00:00:00', 'Work'),
('Agent1', '05.04.2022 00:00:00', 'Work'),
('Agent1', '05.04.2022 00:00:00', 'Work'),
('Agent1', '05.04.2022 00:00:00', 'Work'),
('Agent1', '06.04.2022 00:00:00', 'Work'),
('Agent1', '06.04.2022 00:00:00', 'Work'),
('Agent1', '06.04.2022 00:00:00', 'Work'),
('Agent1', '08.04.2022 00:00:00', 'Work'),
('Agent1', '08.04.2022 00:00:00', 'Work'),
('Agent1', '08.04.2022 00:00:00', 'Work'),
('Agent1', '09.04.2022 00:00:00', 'Work'),
('Agent1', '09.04.2022 00:00:00', 'Work'),
('Agent1', '09.04.2022 00:00:00', 'Work'),
('Agent1', '10.04.2022 00:00:00', 'Work'),
('Agent1', '11.04.2022 00:00:00', 'Work'),
('Agent1', '11.04.2022 00:00:00', 'Work'),
('Agent1', '11.04.2022 00:00:00', 'Other absence'),
('Agent1', '13.04.2022 00:00:00', 'Work'),
('Agent1', '13.04.2022 00:00:00', 'Work'),
('Agent1', '13.04.2022 00:00:00', 'Work'),
('Agent1', '13.04.2022 00:00:00', 'SickLeave'),
('Agent1', '14.04.2022 00:00:00', 'SickLeave'),
('Agent1', '14.04.2022 00:00:00', 'Work'),
('Agent1', '15.04.2022 00:00:00', 'Holiday'),
('Agent1', '15.04.2022 00:00:00', 'SickLeave'),
('Agent1', '18.04.2022 00:00:00', 'SickLeave'),
('Agent1', '18.04.2022 00:00:00', 'Work'),
('Agent1', '19.04.2022 00:00:00', 'Work'),
('Agent1', '19.04.2022 00:00:00', 'Work'),
('Agent1', '19.04.2022 00:00:00', 'Work'),
('Agent1', '19.04.2022 00:00:00', 'SickLeave'),
('Agent1', '20.04.2022 00:00:00', 'SickLeave'),
('Agent1', '20.04.2022 00:00:00', 'Work'),
('Agent1', '20.04.2022 00:00:00', 'Work'),
('Agent1', '20.04.2022 00:00:00', 'Work'),
('Agent1', '21.04.2022 00:00:00', 'Work'),
('Agent1', '21.04.2022 00:00:00', 'Work'),
('Agent1', '21.04.2022 00:00:00', 'SickLeave'),
('Agent1', '21.04.2022 00:00:00', 'Work'),
('Agent1', '22.04.2022 00:00:00', 'Work'),
('Agent1', '22.04.2022 00:00:00', 'Work'),
('Agent1', '22.04.2022 00:00:00', 'SickLeave'),
('Agent1', '25.04.2022 00:00:00', 'Work'),
('Agent1', '25.04.2022 00:00:00', 'SickLeave'),
('Agent1', '25.04.2022 00:00:00', 'Work'),
('Agent1', '26.04.2022 00:00:00', 'SickLeave'),
('Agent1', '26.04.2022 00:00:00', 'Work'),
('Agent1', '26.04.2022 00:00:00', 'Work'),
('Agent1', '27.04.2022 00:00:00', 'Work'),
('Agent1', '27.04.2022 00:00:00', 'Work'),
('Agent1', '27.04.2022 00:00:00', 'SickLeave'),
('Agent1', '27.04.2022 00:00:00', 'Work'),
('Agent1', '29.04.2022 00:00:00', 'Work'),
('Agent1', '29.04.2022 00:00:00', 'SickLeave'),
('Agent1', '29.04.2022 00:00:00', 'Work'),
('Agent1', '30.04.2022 00:00:00', 'SickLeave'),
('Agent1', '30.04.2022 00:00:00', 'Work'),
('Agent1', '30.04.2022 00:00:00', 'Work'),
('Agent1', '30.04.2022 00:00:00', 'Work');

CodePudding user response：

I might have found a solution with a different approach. I'll need to tidy up the code some, but at first pass it seems to produce the desired outcome.

;WITH CTE AS (SELECT LOWER(k1.Agent) AS Agent, CAST(k1.Date AS DATE) AS Date, k1.Category
FROM Table k1
WHERE Category = 'SickLeave'
AND Date >= CONVERT(DATETIME, '01.01.2021 00:00:00', 104))

, CTE2 AS (SELECT LOWER(k2.Agent) AS Agent, CAST(k2.Date AS DATE) AS Date, k2.Category
FROM Table k2
WHERE Date >= CONVERT(DATETIME, '01.01.2021 00:00:00', 104))

, CTE3 AS (SELECT LOWER(b.Agent) AS Agent, b.Date, (CASE WHEN b.Date = a.Date THEN 1 ELSE 0 END) AS SickLeaveFlg
FROM CTE2 b
LEFT OUTER JOIN CTE a ON b.Date = a.Date AND b.Agent = a.Agent)

,CTE4 AS (SELECT LOWER(Agent) AS Agent, Date, SickLeaveFlg
FROM CTE3
GROUP BY Agent, Date, SickLeaveFlg)

, CTE5 AS (SELECT LOWER(Agent) AS Agent, Date, SickLeaveFlg,
ROW_NUMBER() OVER (PARTITION BY Agent, (CASE WHEN SickLeaveFlg LIKE 1 THEN 'Y' ELSE 'N' END) ORDER BY Date) AS DateSeq,
ROW_NUMBER() OVER (PARTITION BY Agent ORDER BY Date) AS AgentSeq
FROM CTE4)

SELECT  LOWER(Agent) AS Agent,
        Date,
        SickLeaveFlg,
        ROW_NUMBER() OVER (PARTITION BY Agent, (CASE WHEN SickLeaveFlg LIKE 1 THEN 'Y' ELSE 'N' END) ORDER BY Date) AS DateSeq,
        (CASE WHEN SickLeaveFlg = 1 THEN ROW_NUMBER() OVER (PARTITION BY Agent, SickLeaveFlg, AgentSeq - DateSeq ORDER BY Date) ELSE 0 END) AS ResultSeq
FROM CTE5
ORDER BY Date

CodePudding user response：

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 here