;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
