How do I determine if two leave requests (time off) conflict (overlap)

Question

I have an HR system and I need to see if the new leave (time off) request conflicts with an existing one. I have done this based on the entire day, but I need to do the comparison based on half days...

Here is my data structure (and the data format) This is all done in 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

So I pass this info to the DB 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...

The general check for an overlap is: `Start1 <= End2 and Start2 <= End1`. — HABO, Aug 18 '15 at 13:54
This doesn't work if the date ranges are identical!!! Check it out... let me know if I'm wrong... that happened once ;-) — Danimal111, Aug 18 '15 at 16:54
`declare @Start1 as Date = '19960218', @End1 as Date = '19960315'; declare @Start2 as Date = @Start1, @End2 as Date = @End1; select @Start1 as Start1, @End1 as End1, @Start2 as Start2, @End2 as End2, case when @Start1 <= @End2 and @Start2 <= @End1 then 'overlap' else 'no overlap' end as Status;` Perhaps you had an issue with `DATETIME` values that didn't have matching times. — HABO, Aug 18 '15 at 17:30
I realize where my logic failed me... yes... the solution linked is AWESOME!!! — Danimal111, Aug 19 '15 at 17:17

score 0 · Answer 1 · edited May 23 '17 at 12:21

I realize this has been asked and answered... Algorithm to detect overlapping periods ... Thanks all... this is a dup question. Please disregard... I 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... would have saved me much pain and suffering...

My new startDate will be recorded as "08/22/2015 09:00:00.000" 
                          instead of "08/22/2015 00:00:00.000"  
and the endDate will be              "08/23/2015 13:00:00.000" 
                          instead of "08/23/2015 00:00:00.000"  
I can then compare as per the linked ticket:

How do I determine if two leave requests (time off) conflict (overlap)

1 Answers1