Hi everyone,
Sorry if I miss any details for what I'm trying to achieve.
I work with confidential data so I won't be able to post the details here but I'm running into an issue where my case statement is assigning a value to one bucket when it should be a different one.
I have a piece of code that calculates the % of the limit a member met:
( DEDUCTIBLE_MEDICAL / FAMILY_DED ) = MED_PERCENT_DEDUCTIBLE FORMAT PERCENT.
DEDUCTIBLE_MEDICAL = the $ amount someone incurred
FAMILY_DED = a fixed value based on the product the member is in
Then I use this calculated field (MED_PERCENT_DEDUCTIBLE) to use in a case statement:
CASE
WHEN MED_PERCENT_DEDUCTIBLE <= 0 THEN 'No Deductible'
WHEN 0 < MED_PERCENT_DEDUCTIBLE <= .25 THEN '1st Quartile'
WHEN .25 < MED_PERCENT_DEDUCTIBLE <= .50 THEN '2nd Quartile'
WHEN .50 < MED_PERCENT_DEDUCTIBLE <= .75 THEN '3rd Quartile'
WHEN .75 < MED_PERCENT_DEDUCTIBLE < 1 THEN '4th Quartile'
WHEN MED_PERCENT_DEDUCTIBLE >= 1 THEN 'Hit Deductible'
END AS MED_DEDUCTIBLE_STATUS,
For this example I have someone who has a total of $3,000 for their DEDUCTIBLE_MEDICAL and the FAMILY_DED assigned to them is also $3,000. So based on the calculation, the members MED_PERCENT_DEDUCTIBLE is equal to 1. Based on the case statement I would assume that they would be in the bucket "Hit Deductible" but they are being assigned to the "4th Quartile" bucket instead.
Any thoughts on why this could be? My assumption is that the case statement logic needs to be adjusted but I can't figure out what in particular needs to be changed
Any thoughts or advise would be greatly appreciated!
thank you!
