6

I have an HR system and I need to detect if a new leave (time off) request conflicts with an existing one. I have written code to do this based on the entire day; I now need to do the comparison based on half days.

Here is my data structure (and the data format). I am using SQL Server 2008 R2.

StartDate     (datetime)
EndDate       (datetime)

The following 2 variables show if the user is taking a half or a full day off. The options for each are listed and explained.

StartDateAMPM (int) 0=All Day (StartDate may or may not be the same as the EndDate), 
                    1=AM, (User is out of the office only in the morning)
                    2=PM  (User is out of the office only in the afternoon)

EndDateAMPM   (int) 0="All Day" / "half day" / "StartDate = EndDate", 
                    1=AM (The user is off for the morning only)

**NOTE This cannot be PM (as you cannot end a leave with a half day in the afternoon)

**IF this is a "0", there are 3 possibilities --
A) a full day leave where the StartDate <> EndDate
B) a full day where the StartDate = EndDate
C) a partial day where StartDate = EndDate 
   and the user is off in the morning or the afternoon

I pass this info to the database and am trying to see if the new leave conflicts with existing leave. Here is the code to do it for a single day:

Legend:  @Start DateTime -- StartDate from new leave
         @end   DateTime -- EndDate from new leave
         @StartDateAMPM (int) 0,1,2 (see above for details)
         EndDateAMPM    (int) 0,1   (see above for details)
select count(ID)  
from v_VacReqWHalfDays 
where 
(
        @Start BETWEEN StartDate and EndDate 
    OR  @End   BETWEEN StartDate and EndDate 
    OR  
    (
        @Start <= StartDate 
        AND @End >=EndDate
    )
)
AND kUserID = @UserID;

If Count(ID) > 0, there is a conflict.

My question is how to incorporate the 1/2 day leave. People can take full days or half days off (the morning or the afternoon).

I want to use TSQL to determine if the current leave request conflicts (overlaps) with an existing and can't figure out how to do it.

2

I realize this has been answered before on Stack Overflow. It got me going in the right direction.

I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering.

For anyone in the future who has a similar issue:

  • My new StartDate will be recorded as 08/22/2015 09:00.000 instead of 08/22/2015 00:00.000
  • The EndDate will be 8/23/2015 13:00:00.000

I can then compare as per the linked answer:

(
    (@Start > StartDate and @Start < EndDate)
    OR (@End > StartDate and @End < EndDate)
    OR (@Start < StartDate and @End >EndDate)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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