Finding Data Not In A Set - SQL Server Forums

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

Transact-SQL (2000)

Finding Data Not In A Set

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-24 : 08:54:57

George writes "MS Server 2000
SQL 7 SP4

We have two tables

MySystem.Reservations
MySystem.Clients

The two tables are related by Client_ID

The problem is to determine which clients had reservation in 2004,

MySystem.Reservations.ReservationOpenDate BETWEEN '2004-01-01' AND '2004-12-31'

and did not return in 2005,

MySystem.Reservations.ReservationOpenDate NOT BETWEEN '2005-01-01' AND '2005-12-31'

However NOT BETWEEN includes clients who made reservations in 2003 and 2006

Is it possible with this version of SQL Server to find data NOT in a set?"

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-02-24 : 12:21:59

Yes,

Select R.ID
from Reservations R
join Clients C on C.Client_ID=R.Client_ID
where (R.ReservationOpenDate NOT BETWEEN '2005-01-01' AND '2005-12-31') AND
R.ID in (
Select T.ID
from Reservations T
join Clients C on C.Client_ID=T.Client_ID
where T.ReservationOpenDate BETWEEN '2004-01-01' AND '2004-12-31'
)

That's the best I can do without DDL/DML. But you get the idea I hope.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources