CODEWITHSUNDEEP
sqlintersystems-cache
Amey Banaye
Amey Banaye

Reputation: 3

Cache SQL Recalculate values of joined table for each row in main table

I am working on a cache database query. Since the cache database doesn't support CTE I am using joins to calculate data. The issue is that aggregated data is only calculated for one where the condition for each row in the union. For eg. only the 'Prop decisioned' row is calculated others are always 0. There are many more case conditions.

SELECT
    PT.TYPE_ID, 
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.PROP_CT, 0) END AS PROP_CT,  
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.BEG_BAL_AMT, 0) END AS BEG_BAL_AMT,   
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.BEG_BAL_PCT, 0) END AS BEG_BAL_PCT, 
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.AECB_PROP_CT, 0) END AS AECB_PROP_CT,  
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.AECB_BEG_BAL_AMT, 0) END AS AECB_BEG_BAL_AMT,
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.FSB_PROP_CT, 0) END AS FSB_PROP_CT, 
    CASE WHEN PT.TYPE_ID ='' THEN '' ELSE  COALESCE(main.FCB_BEG_BAL_AMT, 0) END AS FCB_BEG_BAL_AMT
FROM
(    
SELECT 'NOT WORKED and DP Rejects:'  AS TYPE_ID
UNION ALL
SELECT ''
UNION ALL
SELECT 'REJECTS - AGENCY OR PROP ISSUE'
UNION ALL
SELECT 'PROPS DECISIONED'
UNION ALL
SELECT '* ZZ (NO EXP CODE)'
UNION ALL
SELECT 'DECLINED PROPS'
UNION ALL
SELECT 'ACCEPTED PROPS'
)AS PT
LEFT JOIN
(
SELECT
        CASE
        WHEN rawData.ZZCCACCREJ IN ('R','A') THEN 'PROPS DECISIONED'
        WHEN rawData.ZZCCACCREJ = 'R' THEN 'DECLINED PROPS'
        WHEN rawData.ZZCCACCREJ = 'A' THEN 'ACCEPTED PROPS'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE = 'DP' THEN 'NOT WORKED and DP Rejects:'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE IN ('UA','PI','ZB') THEN 'REJECTS - AGENCY OR PROP ISSUE'
        WHEN rawData.ZZCCACCREJ = 'R' AND TRIM(ISNULL(rawData.ZZCCEXPCODE,'')) = '' THEN '* ZZ (NO EXP CODE)'
        END AS TYPE_ID
        ,COUNT(DISTINCT rawData.ZZPACCTID) as PROP_CT
        ,ISNULL(SUM(COALESCE(rawData.ZZAMRTAMEXBEGBAL,rawData.ZZPTOTALBA,rawData.ZZRPPSACCTBAL)),0) AS BEG_BAL_AMT
        ,ROUND(ISNULL(SUM(COALESCE(rawData.ZZAMRTAMEXBEGBAL,rawData.ZZPTOTALBA,rawData.ZZRPPSACCTBAL)) * 100 / NULLIF(totalData.BEG_BAL_AMT,0),0),2) AS BEG_BAL_PCT
        ,SUM(CASE WHEN rawData.ZZFS9FICOSCORE > 0 THEN 1 ELSE 0 END) AS AECB_PROP_CT
        ,ROUND(SUM(CASE WHEN rawData.ZZFS9FICOSCORE > 0 THEN 1 ELSE 0 END) * 100 / NULLIF(totalData.AECB_PROP_CT,0),2)  AS AECB_Prop_PCT
        ,SUM(CASE WHEN rawData.ZZFS9FICOSCORE > 0 THEN  COALESCE(rawData.ZZAMRTAMEXBEGBAL,rawData.ZZPTOTALBA,rawData.ZZRPPSACCTBAL) ELSE 0 END) AS AECB_BEG_BAL_AMT
        ,ROUND(SUM(CASE WHEN rawData.ZZFS9FICOSCORE > 0 THEN  COALESCE(rawData.ZZAMRTAMEXBEGBAL,rawData.ZZPTOTALBA,rawData.ZZRPPSACCTBAL) ELSE 0 END) * 100 
            / NULLIF(totalData.AECB_BEG_BAL_AMT,0),2) AS AECB_BEG_BAL_PCT
        ,SUM(CASE WHEN rawData.ZZFS9FSBIND > 0 THEN 1 ELSE 0 END) AS FSB_PROP_CT
    ,SUM(CASE WHEN rawData.ZZFS9FSBIND > 0 THEN  COALESCE(rawData.ZZAMRTAMEXBEGBAL,rawData.ZZPTOTALBA,rawData.ZZRPPSACCTBAL) ELSE 0 END) AS FCB_BEG_BAL_AMT
FROM (
    SELECT 
        proposal.ZZPACCTID,
        NULLIF(SUM(amortization.ZZAMRTAMEXBEGBAL), 0) AS ZZAMRTAMEXBEGBAL,
        MAX(proposal.ZZPRECVD) AS proposalDate,
       NULLIF(SUM(proposal.ZZPTOTALBA),0) as ZZPTOTALBA
    ,NULLIF(SUM(rppsInfo.ZZRPPSACCTBAL),0) as ZZRPPSACCTBAL
    ,ISNULL(SUM(CASE WHEN zzfiscal9.ZZFS9FICOSCORE IS NULL OR TRIM(zzfiscal9.ZZFS9FICOSCORE) = '' THEN 0 ELSE 1 END),0) as ZZFS9FICOSCORE
    ,ISNULL(SUM(CASE WHEN zzfiscal9.ZZFS9FSBIND IS NULL OR TRIM(zzfiscal9.ZZFS9FSBIND) = '' THEN 0 ELSE 1 END),0) as ZZFS9FSBIND
        ,cdmpinfo.ZZCCEXPCODE,cdmpinfo.ZZCCACCREJ,cdmpinfo.ZZCCSUBCODE
    FROM SQLUser.ARACCOUNT account
    INNER JOIN SQLUser.ARRELATIONSHIP ARREL ON ARREL.ARRELACID = account.ARACID
    INNER JOIN SQLUser.ARENTITY ARENTITY ON ARENTITY.ARENID = account.ARACID
    INNER JOIN SQLUser.ZZRPPSINFO rppsInfo ON account.ARACID = rppsInfo.ZZRPPSACCTID
    INNER JOIN SQLUser.ZZPROPOSAL proposal ON proposal.ZZPACCTID = rppsInfo.ZZRPPSACCTID
    LEFT  JOIN SQLUser.ZZCDMPINFO cdmpinfo ON account.ARACID = cdmpinfo.ZZCCACCTID
    LEFT JOIN SQLUser.ZZAMORTIZATION amortization ON amortization.ZZAMRTACCTID = account.ARACCLTID
    LEFT JOIN SQLUser.ZZFISCAL9 zzfiscal9 ON zzfiscal9.ZZFS9ACCTID = account.ARACID 
    --WHERE rppsInfo.ZZRPPSRECVD BETWEEN DATEADD(DAY, LAST_DAY(DATEADD(MONTH, -24, GETDATE())), 1) AND LAST_DAY(DATEADD(MONTH, -1, GETDATE()))
        --AND cdmpinfo.ZZCCACCREJ = 'R'
    GROUP BY proposal.ZZPACCTID,cdmpinfo.ZZCCACCREJ, cdmpinfo.ZZCCEXPCODE,cdmpinfo.ZZCCSUBCODE
) AS rawData
LEFT JOIN (
    SELECT TOP 1
        COUNT(DISTINCT ZZPACCTID) as PROP_CT
    ,SUM(COALESCE(ZZAMRTAMEXBEGBAL,ZZPTOTALBA,ZZRPPSACCTBAL)) AS BEG_BAL_AMT
    ,SUM(CASE WHEN ZZFS9FICOSCORE > 0 THEN 1 ELSE 0 END) AS AECB_PROP_CT
    ,SUM(CASE WHEN ZZFS9FICOSCORE > 0 THEN  COALESCE(ZZAMRTAMEXBEGBAL,ZZPTOTALBA,ZZRPPSACCTBAL) ELSE 0 END) AS AECB_BEG_BAL_AMT
    ,SUM(CASE WHEN ZZFS9FSBIND > 0 THEN 1 ELSE 0 END) AS FSB_PROP_CT
    ,SUM(CASE WHEN ZZFS9FSBIND > 0 THEN  COALESCE(ZZAMRTAMEXBEGBAL,ZZPTOTALBA,ZZRPPSACCTBAL) ELSE 0 END) AS FCB_BEG_BAL_AMT
    FROM (
        SELECT %NOLOCK DISTINCT
            proposal.ZZPACCTID as ZZPACCTID
            ,MAX(proposal.ZZPRECVD) as proposalDate
            ,NULLIF(SUM(amortization.ZZAMRTAMEXBEGBAL),0) as ZZAMRTAMEXBEGBAL
            ,NULLIF(SUM(proposal.ZZPTOTALBA),0) as ZZPTOTALBA
            ,NULLIF(SUM(rppsInfo.ZZRPPSACCTBAL),0) as ZZRPPSACCTBAL
            ,ISNULL(SUM(CASE WHEN zzfiscal9.ZZFS9FICOSCORE IS NULL OR TRIM(zzfiscal9.ZZFS9FICOSCORE) = '' THEN 0 ELSE 1 END),0) as ZZFS9FICOSCORE
            ,ISNULL(SUM(CASE WHEN zzfiscal9.ZZFS9FSBIND IS NULL OR TRIM(zzfiscal9.ZZFS9FSBIND) = '' THEN 0 ELSE 1 END),0) as ZZFS9FSBIND
        FROM SQLUser.ARACCOUNT account
        INNER JOIN SQLUser.ARRELATIONSHIP ARREL ON ARREL.ARRELACID = account.ARACID
        INNER JOIN SQLUser.ARENTITY ARENTITY ON ARENTITY.ARENID = account.ARACID
        INNER JOIN SQLUser.ZZRPPSINFO rppsInfo ON account.ARACID = rppsInfo.ZZRPPSACCTID
        INNER JOIN SQLUser.ZZPROPOSAL proposal ON proposal.ZZPACCTID = rppsInfo.ZZRPPSACCTID
        LEFT  JOIN SQLUser.ZZCDMPINFO cdmpinfo ON account.ARACID = cdmpinfo.ZZCCACCTID
        LEFT JOIN SQLUser.ZZAMORTIZATION amortization ON amortization.ZZAMRTACCTID = account.ARACCLTID
        LEFT JOIN SQLUser.ZZFISCAL9 zzfiscal9 ON zzfiscal9.ZZFS9ACCTID = account.ARACID
        --WHERE rppsInfo.ZZRPPSRECVD BETWEEN DATEADD(DAY, LAST_DAY(DATEADD(MONTH, -12, GETDATE())), 1) AND LAST_DAY(DATEADD(MONTH, -1, GETDATE()))
        GROUP BY proposal.ZZPACCTID
    ) AS totalData
) AS totalData ON 1=1
WHERE 
        CASE
        WHEN rawData.ZZCCACCREJ IN ('R','A') THEN 'PROPS DECISIONED'
        WHEN rawData.ZZCCACCREJ = 'R' THEN 'DECLINED PROPS'
        WHEN rawData.ZZCCACCREJ = 'A' THEN 'ACCEPTED PROPS'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE = 'DP' THEN 'NOT WORKED and DP Rejects:'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE IN ('UA','PI','ZB') THEN 'REJECTS - AGENCY OR PROP ISSUE'
        WHEN rawData.ZZCCACCREJ = 'R' AND TRIM(ISNULL(rawData.ZZCCEXPCODE,'')) = '' THEN '* ZZ (NO EXP CODE)'
        END  IS NOT NULL
GROUP BY  --23018931.86
    CASE
        WHEN rawData.ZZCCACCREJ IN ('R','A') THEN 'PROPS DECISIONED'
        WHEN rawData.ZZCCACCREJ = 'R' THEN 'DECLINED PROPS'
        WHEN rawData.ZZCCACCREJ = 'A' THEN 'ACCEPTED PROPS'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE = 'DP' THEN 'NOT WORKED and DP Rejects:'
        WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE IN ('UA','PI','ZB') THEN 'REJECTS - AGENCY OR PROP ISSUE'
        WHEN rawData.ZZCCACCREJ = 'R' AND TRIM(ISNULL(rawData.ZZCCEXPCODE,'')) = '' THEN '* ZZ (NO EXP CODE)'
    END
) AS main on pt.type_id= main.type_id

Output

Upvotes: 0

Views: 42

Answers (1)

CoSpringsGuy
CoSpringsGuy

Reputation: 1645

The way your case statement is written the results seem to be what I would expect. Once rawData.ZZCCACCREJ is evaluated as either R or A the result will be PROPS DECISIONED. The logic in the case statement will not go to the next WHEN.

I'm not familiar with your data of course but the below change might be helpful?

CASE
WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE = 'DP' THEN 'PROPS DECISIONED - NOT WORKED and DP Rejects:'
WHEN rawData.ZZCCACCREJ = 'R' AND rawData.ZZCCEXPCODE IN ('UA','PI','ZB') THEN 'PROPS DECISIONED - REJECTS - AGENCY OR PROP ISSUE'
WHEN rawData.ZZCCACCREJ = 'R' AND TRIM(ISNULL(rawData.ZZCCEXPCODE,'')) = '' THEN 'PROPS DECISIONED - * ZZ (NO EXP CODE)'
WHEN rawData.ZZCCACCREJ = 'R' AND TRIM(ISNULL(rawData.ZZCCEXPCODE,'')) NOT IN ('UA','PI','ZB','DP','') THEN 'PROPS DECISIONED - ?? Is there another value that could be populated?'
WHEN rawData.ZZCCACCREJ = 'A' THEN 'PROPS DECISIONED - ACCEPTED PROPS'
END AS TYPE_ID

Upvotes: 0

Related Questions