One to Many Date Filter - 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 2005 Forums

Transact-SQL (2005)

One to Many Date Filter

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-01 : 14:38:19

Good afternoon. I have a query that needs to filter out any records that do not have a modifydate entry after 1/1/2009

Here are the code:

select a.account,b.modifydate
from sql1.sysdba.Account A
left outer join sql1.sysdba.Opportunity B ON b.accountid = b.accountid
where b.modifydate < '2009-01-01 00:00:00.00'

This code works except in my test data one record has 168 entries in the opportunity table, and some are greater that the above date. What this means is that record "a" should NOT be on the list/output.

Sorry for the long explanation, any ideas ?

Thanks

Bryan Holmstrom

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 16:37:18

[code]SELECT
a.account,b.modifydate
FROM
sql1.sysdba.Account A
inner join sql1.sysdba.Opportunity B
ON b.accountid = b.accountid
WHERE NOT EXISTS
(
SELECT * FROM sql1.sysdba.Opportunity x
WHERE x.modifydate >= '20090101'
AND x.accountid = b.accountid
);[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources