case statements return same value

[hexoffender]

I have these 4 case statements 

count(*) as Total_claim_reciepts,

count(case when claim_id like '%M%' and receipt_flag = 1 and is_firstpassclaim = 1 then 0 else claim_id end) as Total_claim_reciepts,

count(case when claim_status ='DENIED' and claim_repaid =0 and production_flag=1 then claim_id else 0 end ) as Total_claims_Denied_volume,

count(case when claim_status ='DENIED' then claim_id else 0 end ) as Total_claims_Denied_volume,

and they all return the same value from this query:

;with CTE1 as (

Select

c.part_state as Market,    

concat(Month(c.part_snap),'-',Year(c.part_snap)) as Claim_Mnth_Yr,

left(rc.ratecode,5) as CMS_contract,

c.claim_id,

c.is_firstpassclaim,

c.claim_repaid,

c.production_flag,

c.receipt_flag,

c.claim_billedamount,

c.claim_status,

c.claim_receiveddate, c.claim_paiddate

from  enterprise_db.claim_snap c  

join sourcedata_db.qnxt_enrollkeys ek  on  c.member_id=ek.memid

  and c.claim_enrollid =ek.enrollid

  and c.claim_receiveddate between ek.effdate and ek.termdate

  and c.source_state=ek.source_state

join reference_db.core_crosswalk_ratecode rc on rc.planid=ek.planid

  and ek.programid = rc.programid

  and rc.source_state=ek.source_state

Where c.part_state ='CA'

and  c.part_snap  between '2023-01-01 00:00:00.000' and getdate()

and left(rc.ratecode ,5) in(

'H0490','H1799','H2224','H2478','H2533','H2715','H2879','H3038','H5280','H5588','H5628','H5810','H5823','H5926','H5992','H7559',

'H7678','H7844','H8046','H8130','H8176','H8197','H8423','H8677','H8845','H8870','H9082','H9545','H9870','H9955')

) Select

CTE1.market, CTE1.CMS_contract, CTE1.Claim_Mnth_Yr,

count(*) as Total_claim_reciepts,

count(case when claim_id like '%M%' and receipt_flag = 1 and is_firstpassclaim = 1 then 0 else claim_id end) as Total_claim_reciepts,

count(case when claim_status ='DENIED' and claim_repaid =0 and production_flag=1 then claim_id else 0 end ) as Total_claims_Denied_volume,

count(case when claim_status ='DENIED' then claim_id else 0 end ) as Total_claims_Denied_volume,

sum(case when claim_id like '%M%' and receipt_flag = 1 and is_firstpassclaim = 1 then 0 else claim_billedamount end) as total_billed_amount,

AVG( datediff(day,claim_receiveddate, claim_paiddate)) as Average_TAT_cal_days

 from CTE1

Group by Market,    

Claim_Mnth_Yr ,

CMS_contract

order by market, CMS_contract, Claim_Mnth_Yr

[Kaniz]

Hi @hexoffender, It seems that the four case statements contain some logic overlap, which is probably causing the same output to be produced for all four cases.

For example, the second case statement counts the total number of claim receipts where claim_id contains 'M', receipt_flag is 1, and is_firstpassclaim is 1.

However, this logic is already implied in the total count calculation in the first case statement, which counts all rows regardless of the claim_id or the receipt flag. Similarly, the third case statement checks if the claim is denied, claim_repaid is 0, and production flag is 1. However, this condition is already covered by the fourth case statement, which counts all denied claims, regardless of other conditions. It seems that these case statements could be simplified to achieve the same output.

Here is one way to simplify the case statements:

count(*) as Total_claim_reciepts, count(case when claim_status ='DENIED' and claim_repaid =0 then claim_id end) as Total_claims_Denied_volume, sum(case when claim_id like '%M%' and receipt_flag = 1 and is_firstpassclaim = 1 then 0 else claim_billedamount end) as total_billed_amount, AVG(datediff(day,claim_receiveddate, claim_paiddate)) as Average_TAT_cal_days.

These case statements calculate the total number of claim receipts, the total number of denied claims, the total billed amount (excluding claims with claim_id containing 'M', receipt_flag is 1, and is_firstpassclaim is 1), and the average TAT in calendar days. You can adjust this logic, based on your specific requirements.