Scaver - 2 years ago 70
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'
``````

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