SQL - Group vacations in a table based on a holidays-CodePudding

Here is the sample data from the employee vacation table.

Emp_id   Vacation_Start_Date    Vacation_End_Date Public_Hday
1234         06/01/2022            06/07/2022       null
1234         06/08/2022            06/14/2022       null
1234         06/15/2022            06/19/2022       06/17/2022
1234         06/20/2022            06/23/2022       null
1234         06/24/2022            06/28/2022       null
1234         06/29/2022            07/02/2022       06/30/2022
1234         07/03/2022            07/07/2022       null
1234         07/08/2022            07/12/2022       null
1234         07/13/2022            07/17/2022       07/15/2022
1234         07/18/2022            07/22/2022       null

I want to group these vacations based on the public holidays in between (Assuming that all the vacations are consecutive). Here is the output that I am trying to get.

   Emp_id   Vacation_Start_Date    Vacation_End_Date  Public_Hday    Group
    1234         06/01/2022            06/07/2022       null           0
    1234         06/08/2022            06/14/2022       null           0
    1234         06/15/2022            06/19/2022       06/17/2022     1
    1234         06/20/2022            06/23/2022       null           1 
    1234         06/24/2022            06/28/2022       null           1
    1234         06/29/2022            07/02/2022       06/30/2022     2
    1234         07/03/2022            07/07/2022       null           2
    1234         07/08/2022            07/12/2022       null           2
    1234         07/13/2022            07/17/2022       07/15/2022     3
    1234         07/18/2022            07/22/2022       null           3

Here is the code that I tried

Select *, dense_rank() over (partition by Emp_id order by Public_Hday) - 1 AS Group from Emp_Vacation.

But, it gave the expected group values only to the vacations where the Public_Hday is not null. How do I get the group values to the other vacations.

CodePudding user response：

You can use a conditional sum() over()

Select * 
      ,Grp = sum( case when [Public_Hday] is null then 0 else 1 end ) over (partition by [Emp_id] order by [Vacation_Start_Date])
from YourTable

Results