case statements return same value

hexoffender — Mon, 23 Oct 2023 15:08:48 GMT

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

topic case statements return same value in Get Started Discussions