Teradata:按每个成员的重复DX标志支付的索赔总额

Question

首先，我确信有一种更简洁的方法可以做到这一点，但这是我能够使代码将DX组合成一列的唯一方法。最初它们在单独的列中作为0/1，我需要它们在一列中。我尝试了PIVOT函数，但没能算出来。

问题是我需要支付的金额是基于重复的实例DX的。这听起来违反直觉，但对于这份报告，这正是我所需要的。

例如。如果会员A患有慢性阻塞性肺病、哮喘和糖尿病。该成员的已支付索赔额为40,000，因此我需要该成员的已支付金额反映120,000，以此类推。

代码:

SELECT 
DX_FLAG
,Sum( AMT_PAID) AS PHARM_PAID_AMT
,Count(DISTINCT(MEMBER_AMISYS_NBR)) AS MEMBER_COUNT 
FROM 
(SELECT 
st.MEMBER_AMISYS_NBR 
,ph.PHARMACY_CLAIM_CK 
,ph.AMT_PAID 
,FILL.DATE_DATE AS Fill_Date 
,Coalesce(CASE WHEN DX_ASTHMA = 'ASTHMA' THEN 'Asthma' END,
CASE WHEN DX_COPD = 'COPD' THEN 'COPD' END,
CASE WHEN DX_DIABETES = 'DIABETES' THEN 'DIABETES' END,
CASE WHEN DX_HEART_FAILURE = 'HEART FAILURE' THEN 'HEART_FAILURE' END,
CASE WHEN DX_HYPERTENSION = 'HYPERTENSION' THEN 'HYPERTENSION' END)
AS DX_FLAG
FROM
STATE_OVERALL_MBRS st
JOIN FT_PHARMACY_CLAIM ph ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK AND ph.DELETED_IND = 'N'                        
JOIN DIM_DATE FILL ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
AND ph.PLAN_DIM_CK =10  
AND  ph.REVERSAL_IND = 'N' 
AND ph.AMT_PAID > 0
) rx  

输出如下:

<表类>DX_FLAGPHARM_PAID_AMTMEMBER_COUNTtbody><<tr>糖尿病7000000014144慢性阻塞性肺病382664096641HEART_FAILURE109080002544哮喘12500000030000高血压5290022325

对成员表进行规范化，然后进行join和aggregate;像这样:

SELECT 
DX_FLAG
,Sum(AMT_PAID) AS PHARM_PAID_AMT
,Count(DISTINCT(MEMBER_AMISYS_NBR)) AS MEMBER_COUNT 
FROM 
(SELECT * FROM State_Overall_Members
UNPIVOT (has_dx  /* New column to hold the 0 or 1 value */ 
FOR DX_FLAG IN (Asthma,COPD,Hypertension,Diabetes,CHF)
/* Original column names become the values in new column DX_FLAG */
) nmlz
WHERE has_dx = 1 /* Only unpivot rows with a 1 in original column */
) st
JOIN FT_PHARMACY_CLAIM ph ON st.MEMBER_CURR_CK = ph.PRESCRIBER_MEMBER_CURR_CK AND ph.DELETED_IND = 'N'                        
JOIN DIM_DATE FILL ON ph.FILL_DATE_DIM_CK = FILL.DATE_DIM_CK
WHERE FILL.DATE_DATE BETWEEN '2021-10-01' AND '2022-09-30'  
AND ph.PLAN_DIM_CK =10  
AND  ph.REVERSAL_IND = 'N' 
AND ph.AMT_PAID > 0
GROUP BY DX_FLAG;

规范化成员表的另一个选项是为每个DX和UNION表设置一个子查询，如下所示:

... FROM
(SELECT MEMBER_CURR_CK, MEMBER_AMISYS_NBR, AMT_PAID, 'Asthma' (VARCHAR(16)) AS DX_FLAG
FROM State_Overall_Members
WHERE Asthma = 1
UNION ALL
SELECT MEMBER_CURR_CK, MEMBER_AMISYS_NBR, AMT_PAID, 'COPD' (VARCHAR(16)) AS DX_FLAG
FROM State_Overall_Members
WHERE COPD = 1
UNION ALL
...
) st
JOIN ...