add new time slots that doesn't fall under existing timeslots

Question

I have a table with below columns

@StartTime  TIME(0),        
@EndTime  TIME(0)

When I add a new timeslot, I need to check and get list of any overlapping slots exists. New slot start time can be existing time slot end time.

SELECT * 
FROM dbo.TimeSettingDetail cdsd
WHERE 
AND 
(
    (@StartTime between cdsd.StartTime and dateadd(MINUTE, 1, cdsd.EndTime)) 
or 
    (@EndTime between dateadd(MINUTE, 1, cdsd.StartTime) and cdsd.EndTime)
)

This is not working as expected in below scenario.

If a time slot exists for 7 AM to 8 AM, when trying to add a new timeslot from 6 AM to 9 AM.

https://stackoverflow.com/questions/3269434/whats-the-most-efficient-way-to-test-if-two-ranges-overlap — shawnt00, Feb 18 '22 at 17:20
https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964 — shawnt00, Feb 18 '22 at 17:20

score 0 · Answer 1 · answered Feb 18 '22 at 17:21

Two timeslots overlap if A starts before B ends, and ends after B starts:

SELECT
    *
FROM
    dbo.TimeSettingDetail cdsd
WHERE
    @StartTime <= cdsd.EndTime
And
    @EndTime >= cdsd.StartTime
;

The only slightly tricky part is what happens with adjacent time-slots: if one slot ends at 6PM and another starts at 6PM, do you consider them to be overlapping? You may need to use < and > instead of <= and >= to get the exact result you need.

add new time slots that doesn't fall under existing timeslots

1 Answers1