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;
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.