Tracking Continuous Days Of Absence From Work Days Only SQL

I'm trying to create a table which takes dates of when a employee is sick and create a new column to provide a 'sickness ID', which will identify a unique instance of absence over

Solution 1:

As I mention in the comment, just use a WHERE. This is, of course, a blind guess due to a lack of sample data (the sample has no working hours data):

--I prefer CTEs over subqueries
WITH CTE AS(
    SELECT s.date_sick,
           s.employee_number,
           ROW_NUMBER() OVER (PARTITION BY employee_number ORDER BY date_sick) AS rn
    FROM dbo.sickness s)
SELECT C.date_sick,
       C.employee_number,
       DENSE_RANK() OVER (ORDER BY C.employee_number, DATEADD(DAY, -C.rn, C.date_sick)) AS sickness_id,
       wh.workinghours
FROM CTE C
     JOIN dbo.workinghours wh ON C.employee_number = wh.employee_number
WHERE wh.working_hours > 0
ORDER BY C.employee_number,
         C.date_sick;

Solution 2:

I think that using lag() to see if the sickness days are consecutive and then a cumulative sum is a better approach for assigning the sickness id.

I am a little unclear on what you want exactly. But here is one approach:

select date_sick, employee_number,
       sum(case when working_hours > 0 and prev_working_hours > 0 and
                     dateadd(day, -1, date_sick) = prev_date_sick
                then 0 else 1
           end) over (partition by employee_number order by date_sick) as sickness_id
from (select s.*,
             lag(date_sick) over (partition by employee_number order by date_sick) as prev_date_sick,
             lag(working_hours) over (partition by employee_number order by date_sick) as prev_working_hours
      from sickness s left join
           working_hours wh
           on s.date_sick = wh.working_hours
     ) s
order by employee_number, date_sick