CODEWITHSUNDEEP
sqlsql-servert-sql
jimstar
jimstar

Reputation: 21

Not within ranges from another table

I am using MSSQL 2000.

I have a table with a date column containing dates, t_mydates(dateA).

I have a second table t_exlusions(start_date,end_date)

I want a query which returns all dates from t_mydates where they are not between any of the ranges in t_exlusions.

Upvotes: 2

Views: 44

Answers (1)

abatishchev
abatishchev

Reputation: 100248

Query:

select d.*
from @t_mydates d
left join @t_exlusions e on date between start_date and end_date
where start_date is null and end_date is null

Test date:

declare @t_mydates table (date date)
insert into @t_mydates (date)
values
(GETDATE()),(GETDATE()-1),(GETDATE()-2),(GETDATE()-3),(GETDATE()-4),(GETDATE()-5),(GETDATE()-6),(GETDATE()-7)

declare @t_exlusions table (start_date date, end_date date)
insert into @t_exlusions (start_date, end_date)
values
(GETDATE()-1, GETDATE()), (GETDATE()-5, GETDATE()-4)

Result:

2011-08-27
2011-08-26
2011-08-23
2011-08-22

Upvotes: 2

Related Questions