Datespan between a range of other datespans

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Datespan between a range of other datespans

Author

Topic

maxald
Starting Member

7 Posts

Posted - 2010-06-16 : 08:56:35

Hi! I have two lists with datespans (start- and enddates). From my first list I want to check if my datespans is (partly, more or less) between any datesspan in my other list:


List 1:

Startdate | Enddate
2010-01-01 10:00 | 2010-01-01 12:00
2010-01-01 13:00 | 2010-01-01 17:00

List 2:

Startdate | Enddate
2010-01-01 09:00 | 2010-01-01 10:00
2010-01-01 10:00 | 2010-01-01 11:00
2010-01-01 11:00 | 2010-01-01 12:00

In list 1, first date exist in my second list, my other date does not. As a respond true or false (1 or 0) would be great! Is that posible? Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-16 : 08:59:14

quote:
Originally posted by maxald

Is that posible?

Yes.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-16 : 09:03:30

[code]DECLARE @List1 TABLE
(
Startdate DATETIME,
Enddate DATETIME
)

INSERT @List1
SELECT '2010-01-01 10:00', '2010-01-01 12:00' UNION ALL
SELECT '2010-01-01 13:00', '2010-01-01 17:00'

DECLARE @List2 TABLE
(
Startdate DATETIME,
Enddate DATETIME
)

INSERT @List2
SELECT '2010-01-01 09:00', '2010-01-01 10:00' UNION ALL
SELECT '2010-01-01 10:00', '2010-01-01 11:00' UNION ALL
SELECT '2010-01-01 11:00', '2010-01-01 12:00'

SELECT l1.StartDate,
l1.EndDate,
COALESCE(e.found, 0) AS RangeExists
FROM @List1 AS l1
OUTER APPLY (
SELECT TOP(1) 1
FROM @List2 AS l2
WHERE l2.StartDate <= l1.EndDate
AND l2.EndDate >= l1.StartDate
) AS e(found)[/code]

N 56°04'39.26"
E 12°55'05.63"

maxald
Starting Member

7 Posts

Posted - 2010-06-16 : 09:22:34

Huge thanks Peso, works perfectly well!

Outer apply, what is that? Never heard of!?