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.
| Author |
Topic |
|
laailalalaa
Yak Posting Veteran
57 Posts |
 Posted - 2010-04-25 : 16:43:31
|
| i have table T(id, startdate1, enddate1, startdate2, enddate2). i need to write a select that displays:[id, startdate, enddate], where startdate and enddate delimit the intersection between [startdate1, enddate1] and [startdate2, enddate2].any ideas on how to proceed?thanks |
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-04-25 : 17:01:57
|
| SELECT id, MAX(s) AS startdate, MIN(e) AS enddateFROM(SELECT id, startdate1, enddate1, 1 FROM tblUNION ALLSELECT id, startdate2, enddate2, 2 FROM tbl)D(id, s, e, k)GROUP BY idHAVING MAX(CASE WHEN k = 1 THEN e END) > MIN(CASE WHEN k = 2 THEN s END)EDIT: Replace MAX with MIN in HAVING clause.NEW EDIT: MAX or MIN in this case are same. |
 |
|
|
laailalalaa
Yak Posting Veteran
57 Posts |
Posted - 2010-04-25 : 17:19:48
|
| brilliant solution :Dthanks a lot ms65g >:D< |
|
|
|
ms65g
Constraint Violating Yak Guru
497 Posts |
Posted - 2010-04-25 : 17:21:34
|
You are welcome  |
|
|
|
|
|
|
|
|
datetime intersection