Omit Record from Select - 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

General SQL Server Forums

New to SQL Server Programming

Omit Record from Select

Author

Topic

MevaD
Starting Member

32 Posts

Posted - 2009-02-27 : 09:55:03

Hi

I am trying to figure out if this is possible:

I have two tables where I'm counting records from one table that were added during a certain time period but need to omit records if a record is found in another table with a date in the same time period.

Example:

========
TableA
--------
ID
OpenDate
Location
other fields...

========
TableB
--------
ID
OpenDate
other fields...

SELECT Count(*) AS Total, TableA.Locaiton FROM TableA
WHERE OpenDate >= '01/01/2008' AND OpenDate <= '12/31/2008'
AND Record NOT found in TableB with an OpenDate during the same time period.
GROUP BY TableA.Location

Any ideas?

Thanks!!
-MD

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-02-27 : 10:18:34

does this do what you're after.....?


declare @A table (id int, opendate datetime,location varchar(20))
insert into @A
values	 (1,'20080201','location1')
	,(2,'20080501','location2')
	,(3,'20090101','location3')

declare @B table (id int, opendate datetime)
insert into @B
values	 (1,'20080601')
	,(2,'20090501')
	,(3,'20090101')
		
select location,COUNT(*)
from @A a
	left join @b b
		on a.id = b.id
		and b.opendate between '20080101' and '20081231'
where a.opendate between '20080101' and '20081231'
and b.id is null
group by location

MevaD
Starting Member

32 Posts

Posted - 2009-02-27 : 10:30:40

elancaster,

That's it. I wasn't sure how to add the OpenDate condition in the JOIN.

Thanks!!

-MD

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-02 : 01:24:26

hi mevad,
try this one also
select location,COUNT(*) from @a a where not exists (select * from @b where id = a.id and opendate between '20080101' and '20081231')
and a.opendate between '20080101' and '20081231'
group by location

select location,COUNT(*) from @a a where id not in (select id from @b where id = a.id and opendate between '20080101' and '20081231')
and a.opendate between '20080101' and '20081231'
group by location

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 02:50:13

quote:

Originally posted by elancaster

does this do what you're after.....?


declare @A table (id int, opendate datetime,location varchar(20))
insert into @A
values	 (1,'20080201','location1')
	,(2,'20080501','location2')
	,(3,'20090101','location3')

declare @B table (id int, opendate datetime)
insert into @B
values	 (1,'20080601')
	,(2,'20090501')
	,(3,'20090101')
		
select location,COUNT(*)
from @A a
	left join @b b
		on a.id = b.id
		and b.opendate between '20080101' and '20081231'
where a.opendate between '20080101' and '20081231'
and b.id is null
group by location