I have a huge query and it runs in about 30 seconds UNTIL I add in ANY additional SUMs in the SELECT statement.
Can somebody please explain to me what I am doing wrong that is causing this to occur?
SELECT
SOURCE,
SR_RCL,
SR_RCL_GRP,
RCL_TEAM_LEAD_GRP,
COUNT(APPLICATION_NBR) AS APPS,
STORE_NAME,
REGION_NAME,
MARKET_NAME,
SUM(REQ_DAY1_IN) REQ_DAY1_IN,
/*
SUM(REQ_YTD_IN) REQ_YTD_IN, ************************ADDING ANY OF THESE SUMS AFTER THE FIRST ONE CAUSES A PROBLEM****************
SUM(REQ_MTD_IN) REQ_MTD_IN,
SUM(REQ_LYTD_IN) REQ_LYTD_IN,
*/
SUM(REQ_LMTD_IN) REQ_LMTD_IN
FROM
(
SELECT
(SELECT
MAX(K0.TRANSACTION_DATE)
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID) DATA_DATE,
CASE
WHEN LENGTH(Y.SOURCE)<1
OR Y.SOURCE IS NULL
THEN 'OTHER'
ELSE
Y.SOURCE END AS SOURCE,
Y.SR_RCL,
CASE WHEN UPPER(Y.SR_RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.SR_RCL) END AS SR_RCL_GRP,
CASE WHEN UPPER(Y.RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL) END AS RCL_GRP,
CASE WHEN UPPER(Y.RCL_TEAM_LEAD) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL_TEAM_LEAD) END AS RCL_TEAM_LEAD_GRP,
Y.APPLICATION_NBR,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'AUTO' OR Y.PRODUCT_NAME_CATEGORY='RV' THEN 'AUTO/RV'
ELSE Y.PRODUCT_NAME_CATEGORY END) PRODUCT_NAME_CATEGORY2,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'HELOC' OR Y.PRODUCT_NAME_CATEGORY='HOME EQ FIXED'
THEN 'LINES AND LOANS'
ELSE 'OTHER' END) PRODUCT_GROUPING,
Y.STORE_NAME,
Y.REGION_NAME,
Y.MARKET_NAME,
Y.METRO_NAME,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) REQ_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN 1 ELSE 0 END) REQ_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN 1 ELSE 0 END) REQ_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) REQ_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) REQ_LMTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LMTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) DEC_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN 1 ELSE 0 END) DEC_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN 1 ELSE 0 END) DEC_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) DEC_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) DEC_LMTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) FUN_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN 1 ELSE 0 END) FUN_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN 1 ELSE 0 END) FUN_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) FUN_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) FUN_LMTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LMTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LMTD_IN,
(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) APR_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN 1 ELSE 0 END) APR_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN 1 ELSE 0 END) APR_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) APR_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) APR_LMTD_IN,
(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LMTD_IN
FROM
(
SELECT
K1.START_DATE AS DATA_DATE,
K1.APPLICATION_NBR APPLICATION_NBR,
K1.Product Product,
K1.PRODUCT_NAME_CATEGORY PRODUCT_NAME_CATEGORY,
K1.PRODUCT_NAME_DETAIL PRODUCT_NAME_DETAIL,
UPPER(CLRR.CLRR_STORE_DESC) STORE_NAME,
UPPER(CLRR.CLRR_REGION_DESC) REGION_NAME,
CASE
WHEN UPPER(CLRR.CLRR_MARKET_DESC) = 'UNKNOWN MARKET' THEN 'ZUNKNOWN MARKET' ELSE UPPER(CLRR.CLRR_MARKET_DESC) END AS MARKET_NAME,
UPPER(CLRR.CLRR_MARKET_DESC) MARKET_NAME_PLAIN,
UPPER(CLRR.METRO_NAME) METRO_NAME,
X.DAY1_START,
X.DAY1_END,
X.MTD_START,
X.MTD_END,
X.YTD_START,
X.YTD_END,
X.LMTD_START,
X.LMTD_END,
X.LYTD_START,
X.LYTD_END,
SOURCE.SOURCE,
CLRR.SR_RCL,
CLRR.RCL,
CLRR.RCL_TEAM_LEAD,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START)
AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)
THEN K0.ORIG_REQUESTED_AMT ELSE 0 END REQ_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END DEC_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Booked_Amt ELSE 0 END FUN_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END APR_DOLLAR_AMT,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND (TRUNC(T_REQ.DAY_DATE) >= X.LYTD_START
AND TRUNC(T_REQ.DAY_DATE) <= X.YTD_END)
THEN TRUNC(T_REQ.DAY_DATE)
ELSE TO_DATE('01-JAN-00','DD-MON-RR')END REQ_DATE,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END DEC_DATE,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Funding_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END FUN_DATE,
CASE
WHEN
TO_NUMBER(P.APPLICATION_STATUS_CODE ) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END APR_DATE
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID
LEFT JOIN WHSUSR.VW_RPT_TIME_CALENDAR T_REQ
ON K1.DAY__APPLICATION_DATE_OID = T_REQ.BE_ID
LEFT JOIN
(SELECT
STORE_OID,
CLRR_MARKET_DESC,
CLRR_REGION_DESC,
CLRR_STORE_DESC,
METRO_NAME,
COALESCE(SR_REGIONAL_CONSUMER_LENDER,'UNKNOWN') SR_RCL,
COALESCE(REG_CONSUMER_LND_TEAM_LEAD, 'UNKNOWN') RCL_TEAM_LEAD,
COALESCE(REGIONAL_CONSUMER_LENDER_I, 'UNKNOWN') RCL
FROM WHSUSR.VW_RPT_STORE_CLRR) CLRR
ON K0.STORE_OID = CLRR.STORE_OID
LEFT JOIN
(SELECT
BE_ID,
CASE
WHEN LENGTH(APP_SOURCE_GROUP)<1 THEN 'OTHER'
ELSE
APP_SOURCE_GROUP END AS SOURCE
FROM WHSUSR.VW_CURR_MAP_APPLICATION_SOURCE
WHERE APPLICATION_SOURCE <> '$NOOID' AND
APP_SOURCE_GROUP <> 'Not classified at this level') SOURCE
ON K1.APPLICATION_SOURCE_OID=SOURCE.BE_ID
LEFT JOIN
(SELECT
BE_ID,
APPLICATION_STATUS,
APPLICATION_STATUS_NAME,
APPLICATION_STATUS_CODE
FROM
WHSUSR.VW_CURR_MAP_APPLICATION
WHERE APPLICATION_STATUS <> '$NOOID'
AND TO_NUMBER(REPLACE(APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7) P
ON K1.APPLICATION_STATUS_OID = P.BE_ID
LEFT JOIN
(SELECT
SYSDATE AS XSYS,
D.DAY1_S AS DAY1_START,
D.DAY1_E AS DAY1_END,
trunc(D.DAY1_S,'mm') AS MTD_START,
D.DAY1_E AS MTD_END,
TRUNC(D.DAY1_S,'YYYY') AS YTD_START,
D.DAY1_E AS YTD_END,
TRUNC(LAST_DAY(D.DAY1_S)-32,'MM') as LMTD_START,
ADD_MONTHS(D.DAY1_S,-1) AS LMTD_END,
TRUNC(TRUNC(D.DAY1_S,'YYYY') -1,'YYYY') AS LYTD_START,
ADD_MONTHS(D.DAY1_E,-12) AS LYTD_END
FROM
(SELECT
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') AS DAY1_S,
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') +1 -(1/24/60/60) AS DAY1_E
FROM DUAL)D
)X
ON
TRUNC(X.XSYS) >= TRUNC(K1.START_DATE)
AND TRUNC(X.XSYS) < TRUNC(K1.END_DATE)
WHERE
(((TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END))
OR
((TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END))
OR
(TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START) AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)))
) Y
)
GROUP BY
SOURCE,
SR_RCL,
RCL_TEAM_LEAD_GRP,
STORE_NAME,
REGION_NAME,
MARKET_NAME
Can somebody please explain to me what I am doing wrong that is causing this to occur?
SELECT
SOURCE,
SR_RCL,
SR_RCL_GRP,
RCL_TEAM_LEAD_GRP,
COUNT(APPLICATION_NBR) AS APPS,
STORE_NAME,
REGION_NAME,
MARKET_NAME,
SUM(REQ_DAY1_IN) REQ_DAY1_IN,
/*
SUM(REQ_YTD_IN) REQ_YTD_IN, ************************ADDING ANY OF THESE SUMS AFTER THE FIRST ONE CAUSES A PROBLEM****************
SUM(REQ_MTD_IN) REQ_MTD_IN,
SUM(REQ_LYTD_IN) REQ_LYTD_IN,
*/
SUM(REQ_LMTD_IN) REQ_LMTD_IN
FROM
(
SELECT
(SELECT
MAX(K0.TRANSACTION_DATE)
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID) DATA_DATE,
CASE
WHEN LENGTH(Y.SOURCE)<1
OR Y.SOURCE IS NULL
THEN 'OTHER'
ELSE
Y.SOURCE END AS SOURCE,
Y.SR_RCL,
CASE WHEN UPPER(Y.SR_RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.SR_RCL) END AS SR_RCL_GRP,
CASE WHEN UPPER(Y.RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL) END AS RCL_GRP,
CASE WHEN UPPER(Y.RCL_TEAM_LEAD) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL_TEAM_LEAD) END AS RCL_TEAM_LEAD_GRP,
Y.APPLICATION_NBR,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'AUTO' OR Y.PRODUCT_NAME_CATEGORY='RV' THEN 'AUTO/RV'
ELSE Y.PRODUCT_NAME_CATEGORY END) PRODUCT_NAME_CATEGORY2,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'HELOC' OR Y.PRODUCT_NAME_CATEGORY='HOME EQ FIXED'
THEN 'LINES AND LOANS'
ELSE 'OTHER' END) PRODUCT_GROUPING,
Y.STORE_NAME,
Y.REGION_NAME,
Y.MARKET_NAME,
Y.METRO_NAME,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) REQ_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN 1 ELSE 0 END) REQ_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN 1 ELSE 0 END) REQ_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) REQ_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) REQ_LMTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LMTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) DEC_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN 1 ELSE 0 END) DEC_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN 1 ELSE 0 END) DEC_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) DEC_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) DEC_LMTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) FUN_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN 1 ELSE 0 END) FUN_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN 1 ELSE 0 END) FUN_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) FUN_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) FUN_LMTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LMTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LMTD_IN,
(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) APR_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN 1 ELSE 0 END) APR_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN 1 ELSE 0 END) APR_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) APR_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) APR_LMTD_IN,
(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LMTD_IN
FROM
(
SELECT
K1.START_DATE AS DATA_DATE,
K1.APPLICATION_NBR APPLICATION_NBR,
K1.Product Product,
K1.PRODUCT_NAME_CATEGORY PRODUCT_NAME_CATEGORY,
K1.PRODUCT_NAME_DETAIL PRODUCT_NAME_DETAIL,
UPPER(CLRR.CLRR_STORE_DESC) STORE_NAME,
UPPER(CLRR.CLRR_REGION_DESC) REGION_NAME,
CASE
WHEN UPPER(CLRR.CLRR_MARKET_DESC) = 'UNKNOWN MARKET' THEN 'ZUNKNOWN MARKET' ELSE UPPER(CLRR.CLRR_MARKET_DESC) END AS MARKET_NAME,
UPPER(CLRR.CLRR_MARKET_DESC) MARKET_NAME_PLAIN,
UPPER(CLRR.METRO_NAME) METRO_NAME,
X.DAY1_START,
X.DAY1_END,
X.MTD_START,
X.MTD_END,
X.YTD_START,
X.YTD_END,
X.LMTD_START,
X.LMTD_END,
X.LYTD_START,
X.LYTD_END,
SOURCE.SOURCE,
CLRR.SR_RCL,
CLRR.RCL,
CLRR.RCL_TEAM_LEAD,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START)
AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)
THEN K0.ORIG_REQUESTED_AMT ELSE 0 END REQ_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END DEC_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Booked_Amt ELSE 0 END FUN_DOLLAR_AMT,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END APR_DOLLAR_AMT,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND (TRUNC(T_REQ.DAY_DATE) >= X.LYTD_START
AND TRUNC(T_REQ.DAY_DATE) <= X.YTD_END)
THEN TRUNC(T_REQ.DAY_DATE)
ELSE TO_DATE('01-JAN-00','DD-MON-RR')END REQ_DATE,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END DEC_DATE,
CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Funding_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END FUN_DATE,
CASE
WHEN
TO_NUMBER(P.APPLICATION_STATUS_CODE ) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END APR_DATE
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID
LEFT JOIN WHSUSR.VW_RPT_TIME_CALENDAR T_REQ
ON K1.DAY__APPLICATION_DATE_OID = T_REQ.BE_ID
LEFT JOIN
(SELECT
STORE_OID,
CLRR_MARKET_DESC,
CLRR_REGION_DESC,
CLRR_STORE_DESC,
METRO_NAME,
COALESCE(SR_REGIONAL_CONSUMER_LENDER,'UNKNOWN') SR_RCL,
COALESCE(REG_CONSUMER_LND_TEAM_LEAD, 'UNKNOWN') RCL_TEAM_LEAD,
COALESCE(REGIONAL_CONSUMER_LENDER_I, 'UNKNOWN') RCL
FROM WHSUSR.VW_RPT_STORE_CLRR) CLRR
ON K0.STORE_OID = CLRR.STORE_OID
LEFT JOIN
(SELECT
BE_ID,
CASE
WHEN LENGTH(APP_SOURCE_GROUP)<1 THEN 'OTHER'
ELSE
APP_SOURCE_GROUP END AS SOURCE
FROM WHSUSR.VW_CURR_MAP_APPLICATION_SOURCE
WHERE APPLICATION_SOURCE <> '$NOOID' AND
APP_SOURCE_GROUP <> 'Not classified at this level') SOURCE
ON K1.APPLICATION_SOURCE_OID=SOURCE.BE_ID
LEFT JOIN
(SELECT
BE_ID,
APPLICATION_STATUS,
APPLICATION_STATUS_NAME,
APPLICATION_STATUS_CODE
FROM
WHSUSR.VW_CURR_MAP_APPLICATION
WHERE APPLICATION_STATUS <> '$NOOID'
AND TO_NUMBER(REPLACE(APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7) P
ON K1.APPLICATION_STATUS_OID = P.BE_ID
LEFT JOIN
(SELECT
SYSDATE AS XSYS,
D.DAY1_S AS DAY1_START,
D.DAY1_E AS DAY1_END,
trunc(D.DAY1_S,'mm') AS MTD_START,
D.DAY1_E AS MTD_END,
TRUNC(D.DAY1_S,'YYYY') AS YTD_START,
D.DAY1_E AS YTD_END,
TRUNC(LAST_DAY(D.DAY1_S)-32,'MM') as LMTD_START,
ADD_MONTHS(D.DAY1_S,-1) AS LMTD_END,
TRUNC(TRUNC(D.DAY1_S,'YYYY') -1,'YYYY') AS LYTD_START,
ADD_MONTHS(D.DAY1_E,-12) AS LYTD_END
FROM
(SELECT
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') AS DAY1_S,
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') +1 -(1/24/60/60) AS DAY1_E
FROM DUAL)D
)X
ON
TRUNC(X.XSYS) >= TRUNC(K1.START_DATE)
AND TRUNC(X.XSYS) < TRUNC(K1.END_DATE)
WHERE
(((TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END))
OR
((TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END))
OR
(TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START) AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)))
) Y
)
GROUP BY
SOURCE,
SR_RCL,
RCL_TEAM_LEAD_GRP,
STORE_NAME,
REGION_NAME,
MARKET_NAME