Excluding Records - 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

Analysis Server and Reporting Services (2005)

Excluding Records

Author

Topic

thehollis
Starting Member

4 Posts

Posted - 2008-04-24 : 16:02:43

Hello,

I'm trying to figure out the best way to approach this seemingly simple task in Reporting Services 2005, but have come up with nothing but frustration. Any help would be appreciated!

Here's the basic idea:

SELECT Server.Database1.Employee, Server.Database1.HomeDept, Server.Database2.WorkedDept
FROM Server.Database1, Server.Database2

I need to exclude certain combinations of HomeDept/WorkedDept (about 11)...such as 1015/2223, 1015/2226, 2002/4422, 2002/8742, 2002/2342, etc. I want any other combinations of HomeDept 1015 & 2002 to be selected (which would be hundreds).

I've tried:

WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) AND Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))

(and all combinations of NOT IN, <>, and parenthesis placement...I even tried to do each exception separately)

What happens is that all instances of HomeDept 1015 and 2002 and all instances of WorkedDept 2223, 2226, 4422, 8742, and 2342 are excluded.

The SQL Query Designer will remove parenthesis so that the HomeDept exclusion is processed separately from the WorkedDept.

I've tried to do the SQL in the Generic Query Designer, so the SQL Query Designer doesn't reformat it, and it still returns the same results.

This behavior only seems to happen when using <> for both, NOT IN for both, or a combo of NOT and <>. If I used = for the HomeDept, the exclusion of the WorkedDept worked fine.

Any suggestions?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-06 : 09:31:33

Try this:


WHERE (Server.Database1.HomeDept <> '1015' AND Server.Database2.WorkedDept NOT IN ('2223','2226')) 
OR (Server.Database1.HomeDept <> '2002' AND Server.Database2.WorkedDept NOT IN ('4422','8742','2342'))

alternatively you could do


select <fieldlist>
from Database1 (surely this should be a table?)
join Database2 (surely this should be a table?)
	on <join condition>
except
select *
from Database1 (surely this should be a table?)
join Database2 (surely this should be a table?)
	on <join condition>
where (Server.Database1.HomeDept = '1015' and Server.Database2.WorkedDept IN ('2223','2226'))
OR (Server.Database1.HomeDept = '2002' AND Server.Database2.WorkedDept IN ('4422','8742','2342'))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources