help with query - 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)

help with query

Author

Topic

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-09 : 21:15:23

Below is a small sample of an issue I have at my company which has millions of records I need to sort through. Basically, each account number is duplicated with a code number next to it and I need to filter account numbers that have a code 1 and 4. It sounds simple, but each time I run the query below, it will still show a row where that account number does not equal 1 or 4. For example, it shows Account number 112123 when it should be off the report. Basically, any account number that a code of 1 and 4 should be removed from the report and have no row at all. How can this be done?

Select Account_Number, Code
from Table1
where Code not in('1','4')

Account_Number Code
112123 1
112123 4
112123 68
113999 1
113999 89
113999 14
114898 5
114898 89
114898 77
114898 45
999888 56
999888 11
999888 87

RESULTS: The results still show account number 112123, but I want it off the report because it had a code of 1.

Account_Number Code

112123 68 - this should be off report because it had a code 1

113999 89
113999 14
114898 5
114898 89
114898 77
114898 45
999888 56
999888 11
999888 87

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-09 : 22:31:17

[code]
SELECT *
FROM Table1 t
WHERE NOT EXISTS (SELECT * FROM Table1 x WHERE x.Account_Number = t.Account_Number and Code in ('1', '4')
[/code]

KH
[spoiler]Time is always against us[/spoiler]

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-09-09 : 22:32:30

Try on of these:


SELECT T1.Account_Number, T1.Code
  FROM Table1 AS T1
 WHERE NOT EXISTS(SELECT *
                    FROM Table1 AS T2
                   WHERE T2.Account_Number = T1.Account_Number
                     AND T2.Code IN('1', '4'));
GO
SELECT Account_Number, Code
  FROM (SELECT Account_Number, Code,
               MIN(CASE WHEN Code IN('1', '4') THEN 0 ELSE 1 END) OVER(PARTITION BY Account_Number) AS flag
          FROM Table1) AS T
 WHERE flag = 1

joe8079
Posting Yak Master

127 Posts

Posted - 2011-09-09 : 22:52:06

Awesome, thanks for the quick response. i'll try these on monday.
The database I'm working on has millions of records so this will be very helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources