Detect if two time periods overlap

Question

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.

Answer 1

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)
)

Answer 2

I think this condition covers all the scenarios( of Date Overlaps) for which other's are giving 3 or 4 different conditions,

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

and to get Non Overlappings:

( @Start > EndDate or @End < StartDate )