Add a counter based consecutive dates

This is a gaps and island problem: islands represents consecutive days of leaves, and you want to enumerate the rows of each island.

Here is an approach that uses the date difference against a monotonically increasing counter to build the groups:

select t.*,
    row_number() over(
        partition by employee, dateadd(day, -rn, leave_date)
        order by leave_date
    ) counter
from (
    select t.*,
        row_number() over(partition by employee order by leave_date) rn
    from mytable t
) t
order by employee, leave_date

Demo on DB Fiddle

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top