Menu
I have two tables, both with start time and end time fields. I need to find, for each row in the first table, all of the rows in the second table where the time intervals intersect.
For example:
<-----row 1 interval-------> <---find this--> <--and this--> <--and this--> Please phrase your answer in the form of a SQL WHERE-clause, AND consider the case where the end time in the second table may be NULL.
Target platform is SQL Server 2005, but solutions from other platforms may be of interest also.
560
Basically, a period can be represented by a line fragment on time axis which has two boundaries; starttime and endtime. To claim two time periods to be overlapping, they must have common datetime values which is between lower and upper limits of both periods.
SELECT DATEDIFF(second, '2000-01-01 08:00:00' , '2000-01-01 16:00:00'); Return the difference/interval in seconds. Divide by 60 to get minutes, and divide by 60 again to get hours. Since you are only using the time component, you can set the datepart to whatever you like.
OVERLAPS provides functionally equivalent to the following: EXISTS ( expression INTERSECT expression ) OVERLAPS is one of the Entity SQL set operators. All Entity SQL set operators are evaluated from left to right. For precedence information for the Entity SQL set operators, see EXCEPT.
A datetime or interval data type is stored as a decimal number with a scale factor of zero and a precision equal to the number of digits that its qualifier implies.
SELECT * FROM table1,table2 WHERE table2.start <= table1.end AND (table2.end IS NULL OR table2.end >= table1.start) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
