help with comparison script - 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 2000 Forums

Transact-SQL (2000)

help with comparison script

Author

Topic

transitionsdba
Starting Member

3 Posts

Posted - 2009-01-30 : 12:23:45

I am very new to SQL 2000, having just started as the DBA at my company. I am hoping that someone here will be able to assist me in writing this script.

Here is the gist of what I need to do:

I have a table called screening2
uniqueid_c vchar(18) - Primary Key
clientid_c vchar(18)
addresstype_c vchar(25)
send_to_bills int

under adresstype_c there can be 7 types of addresses. I need to check the data in this table for all clients that have send_to_bills=0 for all 7 types of addresses.

Example:
uniqueid_c = 000000
clientid_c = 123456
addresstype_c = addresstype1
send_to_bills = 0

uniqueid_c = 000001
clientid_c = 123456
addresstype_c = addresstype2
send_to_bills = 1

Since the second row has send_to_bills = 1, this client is being billed. But if send_to_bills = 0 in the second row, then the client is not getting billed and I need this client in the results.

I hope that I explained that clear enough. I appreciate any help with this matter.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:30:44

do you mean this?


select * from screening2 where send_to_bills = 0

transitionsdba
Starting Member

3 Posts

Posted - 2009-01-30 : 12:54:11

That would give me all clients that have a row in the table where send_to_bills=0.

Each of the clients can have all of the 7 addresstype_c values, and I only want clients that all rows associated with them have a send_to_bills=0.

If send_to_bills=1 in any row of a client, I dont want that client to appear in the results.

Hope that helps some

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 13:07:44

[code]select * from screening2 where clientid_c in
(
select clientid_c
from screening2
group by clientid_c
having sum(case when send_to_bills = 1 then 1 else 0 end)=0
)
[/code]

transitionsdba
Starting Member

3 Posts

Posted - 2009-01-30 : 14:02:06

Worked perfectly. Thank you for your assistance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-31 : 08:47:08

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources