Tracking A Continuous Instance Of Absence Sql

I have a table called sickness which is a record of when an employee is off work sick. It looks like this: Date_Sick Employee_Number ---------- ---------------- 2020-06-08

Solution 1:

This is a gaps-and-islands problem.

Here, I think the simplest approach is row_number() and date arithmetics:

select date_sick, employee_number,
    dense_rank() over(orderby employee_number,  dateadd(day, -rn, date_sick)) as sickness_id
from (
    select s.*,
        row_number() over(partitionby employee_number orderby date_sick) as rn
    from sickness s
) s
orderby employee_number, date_sick

This works by comparing date_sick against an incrementing id, then using that information to rank the records.

Demo on DB Fiddle - with credits to Larnu for generating the DDL in the first place:

date_sick  | employee_number | sickness_id
:--------- | :-------------- | ----------:
2020-06-08 | 001             |           1
2020-06-10 | 001             |           2
2020-06-11 | 001             |           2
2020-06-12 | 001             |           2
2020-06-08 | 002             |           3
2020-06-09 | 002             |           3