Scaver Scaver - 7 months ago 14
SQL Question

SQL getting status of a period

I'm looking for a SQL solution for the following problem.
I want a list of employees who are more then 14 days sick in a row.

I've a sql table with the following:

First_name, Last_Name, INDIRECT_ID, SHIFT_DATE
John, Doe, Sick, 2016-01-01
John, Doe, Sick, 2016-01-02
John, Doe, working, 2016-01-03
John, Doe, Sick, 2016-01-04
John, Doe, Sick, 2016-01-05
etc.


I thought to do this by seeing if they are sick for 10x (2x 5 working days) in two weeks. But maybe there is a much simpler solution for it. But Now I'm also getting duplicate answers.

select FIRST_NAME, LAST_NAME
from (select t.*
,(select count(*)
from LABOR_TICKET t2
where t2.EMPLOYEE_ID = t.EMPLOYEE_ID and
t2.INDIRECT_ID = t.INDIRECT_ID and
t2.SHIFT_DATE >= t.SHIFT_DATE and
t2.SHIFT_DATE < DATEADD(day, 14, t.SHIFT_DATE)) NumWithin14Days
from LABOR_TICKET t
where SHIFT_DATE between '2016-01-01' and '2016-04-01'
) LABOR_TICKET
INNER JOIN
EMPLOYEE ON LABOR_TICKET.EMPLOYEE_ID = EMPLOYEE.ID

where NumWithin14Days >= 10 AND INDIRECT_ID = 'SICK'

Answer

Try this, First create all the 14 days intervals in between the From Date and To Date. Then check the count of the 'Sick' is 14 in each interval for every employee.

DECLARE @ST_DATE    DATE='2016-01-01'
        ,@ED_DATE   DATE='2016-04-01'
;WITH CTE_DATE AS (

    SELECT  @ST_DATE AS ST_DATE,DATEADD(DAY,13,@ST_DATE) AS ED_DATE
    UNION ALL
    SELECT  DATEADD(DAY,1,ED_DATE),DATEADD(DAY,14,ED_DATE)
    FROM    CTE_DATE
    WHERE   DATEADD(DAY,14,ED_DATE) <= @ED_DATE
)
SELECT  FIRST_NAME, LAST_NAME
FROM    CTE_DATE
    INNER JOIN LABOR_TICKET ON SHIFT_DATE BETWEEN ST_DATE AND ED_DATE
WHERE INDIRECT_ID = 'Sick'
GROUP BY FIRST_NAME, LAST_NAME
HAVING   COUNT(*) >= 14
Comments