3

I store the employees' vacations in an SQL table as 2 columns of dates (StartDate : EndDate) along with the vacation type. I can't figure out the best approach to check for some rules, every time an employee tries to request a new vacation on the system:

  1. An employee can't connect two vacations unless he/she shows up to work in between.
  2. An employee can connect vacations but with a specific type.

What I tried to do is create a temp calendar table of the full-year dates, and joined the dates of the vacations like this:

Dates DutyState Vacation Type
2023-01-01 Work True A
2023-01-02 Work True A
2023-01-03 Work False NULL
2023-01-04 OFF False NULL
2023-01-05 OFF False NULL
2023-01-06 Work False NULL
2023-01-07 Work False NULL
2023-01-08 Work True B
2023-01-09 OFF False NULL
2023-01-10 OFF False NULL
2023-01-11 Work False NULL

To clarify the rules more:

This employee has a type A vacation from 1 to 2 Jan, then he has another one on 8 Jan of type B, He can request a vacation of any type other than A & B in any other work day(s) in this table (only these 2 types has restrictions), and also can request a vacation of types A or B only on 6 Jan. Only workdays break the connection rule, that's why he can't request type A or B on 11 Jan (Because only Off days in between it and the previous vacation on 8 Jan) also he can't request A or B on 3 Jan (another vacation on 2 Jan connect to it). But he can request type C on any day. How to check for that in SQL?

NOTE: Workdays/Off count may vary. It's not always 3/2, and it's not related to Weekdays or Weekends.

1 Answer 1

2

It's okay, I figured it out: I eliminated the OFF days and then used a combination of LAG/LEAD in a case statement like this:

SELECT *,
    CASE 
        WHEN DATEDIFF(DAY, LAG(Dates, 1) OVER(ORDER BY Dates ASC), Dates) = 1 
            AND LAG(vacID, 1) OVER(ORDER BY Dates ASC) <> vacID 
            AND LAG([Type], 1) OVER(ORDER BY Dates ASC) IN (A, B) 
            AND [Type] IN (A, B) 
            THEN 1 
        WHEN DATEDIFF(DAY, LEAD(Dates, 1) OVER(ORDER BY Dates ASC), Dates) = 1 
            AND LEAD(vacID, 1) OVER(ORDER BY Dates ASC) <> vacID 
            AND LEAD([Type], 1) OVER(ORDER BY Dates ASC) IN (A, B) 
            AND [Type] IN (A, B) 
            THEN 1 
        ELSE 0 
    END AS connects  
    FROM #DT_CalVac WHERE DutyState <> 'OFF';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.