New
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
