cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Get Started Discussions
Start your journey with Databricks by joining discussions on getting started guides, tutorials, and introductory topics. Connect with beginners and experts alike to kickstart your Databricks experience.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

case statements return same value

hexoffender
New Contributor

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
 
 
 
 
 
1 REPLY 1

Kaniz
Community Manager
Community Manager

Hi @hexoffenderIt 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.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!