question

barbs avatar image
barbs asked

eliminate dates from table1 that do not fall into a range of dates in table2

i have a table with following dates: date1 membid 05/24/2008 11 05/31/2008 11 06/07/2008 11 06/10/2008 11 06/12/2008 11 06/14/2008 11 06/17/2008 11 06/19/2008 11 table2 with the range of dates for same person startdte enddte id 05/28/2008 05/28/2008 11 06/09/2008 06/09/2008 11 06/17/2008 06/17/2008 11 or table2 can have dates such as 5/28/2008 5/30/2008 11 I want the dates in table one that are not in any of the ranges in table 2. I can't seem to get this to work. I've tried where date1 startdte or date1>enddte but does not work
t-sqlsql-server-2000tsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Something along these lines? SELECT t1.* FROM t1 OUTER JOIN t2 ON t1.date1 >= t2.startdte AND t1.date1
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
I wasn't online at the time - baking christmas cookies for my son's kindergarten. in spirit i had you both beaten ;)
3 Likes 3 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
+1 beat me to it because I didn't read the question !! How many times must I learn. Read, read and read again.......
1 Like 1 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Just wondering if @WilliamD is OK - surprised he didn't manage to beat both of us to the punch! ;-)
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Good excuse. That's one of the downsides of this contracting malarkey - spending time miles from home...
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
select t1.date1 from table1 t1 left join table2 t2 on t1.membid = t2.id and t1.date1 between t2.startdte and t2.enddte where t2.startdte is null
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Have a consolation point for including the membid / id in the join criteria!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.