March 11, 2015 at 9:00 am
Hi All,
I have a table like below:
Claim Number|Status
=============
100 | 1
100 | 3
101 | 1
102 | 1
102 | 1
102 | 3
103 | 3
104 | 3
105 | 1
105 | 3
1 is approved; 3 is denied in the status column
what will be the SQL Query to get fully denied claims.
so the output should be
Claim Number
=========
103
104
Thanks in advance
March 11, 2015 at 9:03 am
"Fully denied" would be claims that have 1 or more "3" and 0 "1", correct?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2015 at 9:07 am
Here's a ultra-simplistic version.
- Adam
;with Claim_Status as
(
select 100 as [Claim Number] , 1 as [Status]
union all select 100, 3
union all select 101, 1
union all select 102, 1
union all select 102, 1
union all select 102, 3
union all select 103, 3
union all select 104, 3
union all select 105, 1
union all select 105, 3
)
SELECTdistinct CS.[Claim Number]
FROMClaim_StatusCS
WHERECS.Status = 3
EXCEPT
SELECTdistinct CS.[Claim Number]
FROMClaim_StatusCS
WHERECS.Status <> 3-- or you could use: where CS.Status = 1
** Edited for consistent formatting
March 11, 2015 at 9:21 am
After further thought, this one might be more flexible for you.
Again - I thought there might be more status codes you need to filter out other than just "1" so I went with "when CS.Status <> 3" for the "not fully denied." Your requirements may not reflect that.
;with Claim_Status as
(
select 100 as [Claim Number] , 1 as [Status]
union all select 100, 3
union all select 101, 1
union all select 102, 1
union all select 102, 1
union all select 102, 3
union all select 103, 3
union all select 104, 3
union all select 105, 1
union all select 105, 3
)
SELECTCS.[Claim Number]
FROMClaim_StatusCS
GROUP BYCS.[Claim Number]
HAVINGCOUNT(case when CS.Status <> 3 then 1 else null end ) = 0
March 11, 2015 at 10:00 am
LoudClear (3/11/2015)
Here's a ultra-simplistic version.- Adam
;with Claim_Status as
(
select 100 as [Claim Number] , 1 as [Status]
union all select 100, 3
union all select 101, 1
union all select 102, 1
union all select 102, 1
union all select 102, 3
union all select 103, 3
union all select 104, 3
union all select 105, 1
union all select 105, 3
)
SELECTdistinct CS.[Claim Number]
FROMClaim_StatusCS
WHERECS.Status = 3
EXCEPT
SELECTdistinct CS.[Claim Number]
FROMClaim_StatusCS
WHERECS.Status <> 3-- or you could use: where CS.Status = 1
** Edited for consistent formatting
You don't need the distinct keywords. The EXCEPT operator will remove duplicates.
And just to note that you don't begin CTEs with a semi-colon, you terminate the previous statement (and all if possible) with it.
March 11, 2015 at 10:14 am
Luis Cazares (3/11/2015)
And just to note that you don't begin CTEs with a semi-colon, you terminate the previous statement (and all if possible) with it.
I knew when I wrote it that you would chime in and say that, but since:
a) There was no previous statement
b) I don't know how the OP might utilize this code downstream
I elected to put it in anyway.
But be happy that you are the little birdie in my head that makes me actually consider these things before I post.
March 11, 2015 at 11:29 am
LoudClear (3/11/2015)
Luis Cazares (3/11/2015)
And just to note that you don't begin CTEs with a semi-colon, you terminate the previous statement (and all if possible) with it.
I knew when I wrote it that you would chime in and say that, but since:
a) There was no previous statement
b) I don't know how the OP might utilize this code downstream
I elected to put it in anyway.
But be happy that you are the little birdie in my head that makes me actually consider these things before I post.
I wouldn't have a problem with it if it didn't cause problems in several occasions such as the creation of views and in-line table-valued functions or using IF without BEGIN...END.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply