SQL date range intersection tables

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 2005 Forums

Transact-SQL (2005)

SQL date range intersection tables

Author

Topic

homer7_9
Starting Member

1 Post

Posted - 2011-12-22 : 15:59:25

I have two table

TAB A
IdTicket,Date_Start,Date_End
1, 1/1/2011 08:00:00 , 1/1/2011 10:00:00
1, 1/1/2011 10:00:00 , 1/1/2011 11:00:00
1, 1/1/2011 13:00:00 , 1/1/2011 15:00:00
2, 1/2/2011 08:00:00 , 1/2/2011 10:00:00
2, 1/2/2011 12:00:00 , 1/3/2011 11:00:00

TAB B
IdTicket,Date_Start,Date_End
1, 1/1/2011 09:00:00 , 1/1/2011 10:30:00
1, 1/1/2011 12:00:00 , 1/1/2011 15:00:00
2, 1/2/2011 10:00:00 , 1/2/2011 11:00:00
2, 1/2/2011 10:00:00 , 1/3/2011 08:00:00

In SQL I want date range intersection

1, 1/1/2011 09:00:00 , 1/1/2011 10:30:00
1, 1/1/2011 13:00:00 , 1/1/2011 15:00:00
2, 1/2/2011 12:00:00 , 1/3/2011 08:00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-23 : 01:29:47

[code]
SELECT a.IdTicket,
CASE WHEN MIN(a.Date_Start) < MIN(b.Date_Start) THEN MIN(a.Date_Start) ELSE MIN(b.Date_Start) END AS Date_Start,
CASE WHEN MAX(a.Date_End) > MIN(b.Date_End) THEN MAX(a.Date_End) ELSE MAX(b.Date_End) END AS Date_End
FROM TabA a
INNER JOIN TabB b
ON a.IdTicket = b.IdTicket
AND (b.Date_Start BETWEEN a.Date_Start AND a.Date_End
OR b.Date_End BETWEEN a.Date_Start AND a.Date_End)
GROUP BY a.IdTicket
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-23 : 09:10:36

Visakh, doesn't the query you posted calculate the union rather than the intersection?

From what I recall, Joe Celko would advise to use a calendar table with time segments small enough to satisfy your requirements and then use that to find the intersection. That sounds simple enough.

It would be nice to use some kind of logic to find the intersection, but everything that comes to my mind are so convoluted that I would be ashamed to post those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources