How to identify consecutive working dates?

Question

I'm attempting to write some code that groups holidays together and creates a unique ID for each grouping. The trouble i'm having is creating the groups to know that they belong together.

By default if someone requests a week off the 5 days will get a specific ID (ie 223). So there would be a holiday instance of 5 days with ID 223 and I can get start and end date easy enough.

But if someone decides to book a Friday off (ID 224) and then ask for Wednesday and Thursday off (ID 228) and then asks for the following Monday off (ID 230) I then have 3 separate instances that are actually one holiday and here is where I need to group them together and ignore a weekend.

I've tried variations of DENSE_RANK OVER PARTION and ROW_NUMBER OVER ORDER however I cannot get it to return the figure i need.

Sample data and desired output

SELECT '01-March-2017' AS HolidayDate, DATEPART(WK,'01-March-2017') AS Dw, 223 AS ID_Field, 1000 AS HolidayID
UNION ALL
SELECT '02-March-2017' AS HolidayDate, DATEPART(WK,'02-March-2017') AS Dw, 223, 1000
UNION ALL
SELECT '03-March-2017' AS HolidayDate, DATEPART(WK,'03-March-2017') AS Dw, 223, 1000
UNION ALL
SELECT '24-March-2017' AS HolidayDate, DATEPART(WK,'24-March-2017') AS Dw, 230 , 1001
UNION ALL
SELECT '27-March-2017' AS HolidayDate, DATEPART(WK,'27-March-2017') AS Dw, 235, 1001
UNION ALL
SELECT '20-Sep-2017' AS HolidayDate, DATEPART(WK,'20-Sep-2017') AS Dw, 224, 1002
UNION ALL
SELECT '27-Sep-2017' AS HolidayDate, DATEPART(WK,'27-Sep-2017') AS Dw, 228, 1003

So the above creates an example, the HolidayID is the desired column output (numbers chosen at random for sample). the ID field is the number generated when the holiday request is submitted.

As you can see the 223 request came in completed and has the same ID so i can select MAX(Date) and get the desired result grouping by ID_Field.

230 and 235 came in separately but are the same holiday period (as a continual holiday) so I need to group these with the same ID.

finally 225 an 228 are completely separate requests so need unique numbers.

Issue with answer -

Answer 1

When LAG is not supported

with    Holidays 
        as
        (
            select  row_number() over (order by HolidayDate)    as n
                   ,HolidayDate

            from    mytable
        )

select  h.HolidayDate

       ,count
        (
            case
                when    not
                        (
                            datediff (day,p.HolidayDate,h.holidayDate) = 1

                        or  (   datediff (day,p.HolidayDate,h.HolidayDate) = 3
                            and datename (dw,p.HolidayDate) = 'Friday'
                            )
                        )
                then    1
            end
        ) over (order by h.HolidayDate) + 1    as Holiday_id

from                Holidays    as h
        left join   Holidays    as p
        on          p.n = h.n - 1

order by h.HolidayDate

---

When LAG is supported

select  HolidayDate
       ,count(is_gap) over (order by HolidayDate) + 1   as holiday_id

from   (select  HolidayDate

               ,case
                    when    not
                            (
                                datediff (day,prev_HolidayDate,HolidayDate) = 1

                            or  (   datediff (day,prev_HolidayDate,HolidayDate) = 3
                                and datename (dw,prev_HolidayDate) = 'Friday'
                                )
                            )
                    then    1
                end     as is_gap

        from   (select  HolidayDate
                       ,lag(HolidayDate) over (order by HolidayDate) as prev_HolidayDate

                from    mytable
                ) t
        ) t

order by HolidayDate

---

+-------------+------------+
| HolidayDate | holiday_id |
+-------------+------------+
| 2017-03-01  |          1 |
| 2017-03-02  |          1 |
| 2017-03-03  |          1 |
| 2017-03-24  |          2 |
| 2017-03-27  |          2 |
| 2017-09-20  |          3 |
| 2017-09-27  |          4 |
+-------------+------------+