How do I mark rows as longterm illness using T-SQL?

Question

I have a challenge. Our business wants to know if an employees sick day is long term. So if illness is part of 30 concurrent days, all 30 or more records should be marked as [long term illness] = True

Sick days are only Monday - Friday

I have a table where every employee has one row for each calendar day they have illness, like this:

Would this even be possible to solve with T-SQL ?

Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. Usable sample data with DDL and expected results are a start. What have you tried? — HABO, Aug 20 '20 at 12:50
Sure it's possible. It's a gaps and islands problem, search for this, there are plenty of examples to find. Give it a try yourself and if you have issues you can ask for help. — Wouter, Aug 20 '20 at 13:12
Sounds like a homework problem? In reality you'd need a table of dates that excluded holidays as well. You could do something like ROW_NUMBER() or just have an incrementing id in the work days table. Join back to the work days table where the row number or id is +29 for the current row. That gives you the date range for each 30 day period. You could then link to the sick days table and count the rows in that date range to see if it is 30. — Jason Goemaat, Aug 20 '20 at 13:14

score 1 · Accepted Answer · answered Aug 20 '20 at 13:44

So here is a simple solution.

I generate a table with sick or well for each calendar day (s or w)

Then for each day I count the not sick days in the last 30 calendar days, excluding weekends.

If there are 0 well days in that period, then its long term sickness, so for every day we look ahead 29 days to see if we hid long term sickness in the next 30 days. If its a sick day, and within the next 29 days we will hid a period of no well days, then the sick day is a part of a long term sickness.

In my example the second week is a sick week, which is not marked, and april and may ae sick months, all of the days being marked as long term

WITH dates AS /* Generate som dates */
(
    SELECT CAST(DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT 1 n))-1, '2020-01-01') AS DATE) dato
    FROM (VALUES (0), (1), (2), (3), (4)) a (b)
       , (VALUES (0), (1), (2), (3), (4)) b (b)
       , (VALUES (0), (1), (2), (3), (4)) c (b)
),
CTE AS /* Set 2nd week and 3,4 month as sick */
(
    SELECT IIF(MONTH(dato) IN (3, 4) OR DATEPART(WEEK, dato) = 2, 's', 'w') state
         , dato
    FROM dates
),
sums AS /* count days of work last 30 days current day included */
(
    SELECT dato
         , State
         , SUM(IIF(State = 'w' AND DATEPART(WEEKDAY, dato) NOT IN (1, 7), 1, 0)) OVER (ORDER BY dato ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) NonSickDays
    FROM CTE
),
periods AS /* Check if no well days for any day in the next 30 days, current day included */
(
    SELECT dato
         , State
         , MIN(NonSickDays) OVER (ORDER BY dato ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING) Minperiod
         , NonSickDays
    FROM sums
)
SELECT dato /* id sick day, and minimum well days is 0 then its a long term day */
     , State
     , IIF(State = 's' AND Minperiod = 0, 'l', '') longtimesick
     , NonSickDays
FROM periods

Thanks - I can really use this :-) – Tom Brox Aug 23 '20 at 18:26 — Tom Brox, Aug 23 '20 at 18:26

How do I mark rows as longterm illness using T-SQL?

1 Answers1