Omitting 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

General SQL Server Forums

New to SQL Server Programming

Omitting records

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2009-11-20 : 15:03:52

I have the following table:

claimid claimline ruleid status
123 1 611 PEND
123 1 532 PEND
124 2 611 PEND
125 3 611 OKAY
126 1 611 PEND
126 1 532 DENY
127 2 611 PEND
127 2 532 OKAY
I need to pull all of the claimids with 6ll codes with a pend status (which I have done) but exclude any claimids with 611s that have a 532 for the same claimline number that is PEND or DENY, and include the claimid with the 532 with the same claimline that has an OKAY status.

I appreciate any help with this.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-20 : 15:23:35

quote:
Originally posted by lbeese
I need to pull all of the claimids with 6ll codes with a pend status (which I have done)

WHERE ClaimId = 611 AND status = 'PEND'

quote:

but exclude any claimids with 611s that have a 532 for the same claimline number that is PEND or DENY

WHERE NOT EXISTS (SELECT * FROM <table> i WHERE claimline = 532 AND o.claimid = i.claimid)

quote:

, and include the claimid with the 532 with the same claimline that has an OKAY status.

??????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 00:45:04

[code]SELECT t.*
FROM Table t
JOIN (SELECT claimid
FROM Table
GROUP BY claimid
HAVING SUM(CASE WHEN ruleid=611 AND status IN ('PEND','DENY') THEN 1 ELSE 0 END)=0)t1
ON t1.claimid=t.claimid
WHERE t.ruleid=611
AND t.status='PEND'
[/code]

lbeese
Starting Member

24 Posts

Posted - 2009-11-23 : 13:33:17

Thanks, for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 13:42:11

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources