Query help - 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 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

fchachar
Starting Member

15 Posts

Posted - 2012-10-22 : 19:33:10

Hi I have a table with a field called doctype, where all different types of documents gets scanned what I want to do is run a query which shows me only when 'DOCUMENT1' is there but not Document2, 3, 4. So here is what I wrote

select * from document_table a
Where a.archive_date >= '10/22/2012'
and a.doctype = 'DOCUMENT1' and a.doctype NOT IN ('DOCUMENT2', 'DOCUMENT3', 'DOCUMENT4')

The above query does'nt seem to be working fine, any suggestions please?..

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-22 : 20:12:51

There needs to be a column or columns that make a record that you want to search for

SELECT <unique column or columns>
FROM yourTable
WHERE archive_data >= '20121022'
GROUP BY <unique column or columns>
HAVING SUM(CASE WHEN doctype = 'DOCUMENT1' THEN 1 ELSE 0 END) > 0
and sum(CASE WHEN doctype in ('DOCUMENT2', 'DOCUMENT3', 'DOCUMENT4') THEN 1 ELSE 0 END) = 0

You may need to join back this result to get all of the columns that you need

Jim

Everyday I learn something that somebody else already knew

fchachar
Starting Member

15 Posts

Posted - 2012-10-23 : 08:50:31

Thanks alot I will try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources