-2

I have two sets of times of which, we need to check some overlapping occurs or not. For eg. (BeginTime_1, EndTime_1) and (BeginTime_2, EndTime_2) are the two sets of timings. I need to find whether these timings overlap or not. Is it possible to do using SQL. I have achieved it using C#, and now I need to do it using SQL. Is there any builtin function for achieving this? Any help will be highly appreciated. (I'm considering only time in hh:mm:ss)

Anish V
  • 673
  • 3
  • 17
  • 38
  • How you keep the data, and what is type of that columns? – Hamlet Hakobyan May 12 '14 at 16:20
  • Aside from data type, what, if any, assumptions can we make about the data (e.g., is BeginTime_1 always before BeginTime_2, etc.)? – Matt McHugh May 12 '14 at 16:23
  • It may be in varchar with 'hh:mm:ss'. Also, we need to handle about the validations of begintime. I think, this need to be done using some custom function ? – Anish V May 12 '14 at 16:26
  • I'm talking about the time overlapping not for date. @CharlesBretana – Anish V May 12 '14 at 16:28
  • Same principle applies. Whether its date, time, position along a measuring stick, rectangles, whatever. Look at the code in stackoverflow.com/questions/325933/… If your problem is in parsing the time data so that it is indeed time, not character data, that's a different problem. – Charles Bretana May 12 '14 at 16:30
  • I have some time slots/shifts which have some timings allotted. There may be more than 1 shifts. The entire time limit is 24 hrs. SO, I'm trying to check whether these shift timing overlaps or not within that time period? (24 hr) – Anish V May 12 '14 at 16:35
  • @AnishV since you're using SQL Server 2008, you can declare your columns as using the `time` datatype. In this case there is no date, and [my answer](http://stackoverflow.com/a/23614407/224704) can be used as is. But if you need to include the date in those columns, you'll need to modify the query to strip out the date portion of the values. However, this would prevent the query from being able to leverage indexes. – Disillusioned May 13 '14 at 09:36

1 Answers1

1

The query uses the following logic:

  • If an EndTime occurs between the other Begin and End times, then they overlap.
  • Similarly if a BeginTime occurs between the other Begin and End times, then they overlap.

NOTE: It doesn't consider NULL values because even if any of the columns are NULLABLE you'd need to clarify how NULLS should be interpretted. E.g. NULL EndTime might imply it is still ongoing and should assume current time as EndTime.

SELECT  *
FROM    TheTable
WHERE   (EndTime_1 BETWEEN BeginTime_2 AND EndTime_2)
    OR  (EndTime_2 BETWEEN BeginTime_1 AND EndTime_1)
    OR  (BeginTime_1 BETWEEN BeginTime_2 AND EndTime_2)
    OR  (BeginTime_2 BETWEEN BeginTime_1 AND EndTime_1)

Another approach is:

  • If there's no overlap, then one of the BeginTimes must be greater than the other EndTime.
  • So there is an overlap if the above condition is not true.

This assume begin/end times are logically consistent. I.e. you never begin before you end.

SELECT  *
FROM    TheTable
WHERE   NOT (BeginTime_1 > EndTime_2 OR BeginTime_2 > EndTime_1)

Applying logic rules, the WHERE clause can be changed to:

WHERE   BeginTime_1 <= EndTime_2 AND BeginTime_2 <= EndTime_1
Disillusioned
  • 14,635
  • 3
  • 43
  • 77