What I understand about SPOOL is to store data into a temporary table so SQL Server could use it when have CTE or other usage. Does that means SPOOL should appear in the middle of my execution plan?
Edit: Execution Plan uploaded
WITH FXSPOT AS (
SELECT
CURRENCY1
, FXRATE
FROM
#DBNAME#.DBO.#TBL1#
WHERE
FXSPOT.DATA_DATE = '2016-10-01'
), FTP_MTD_PRE AS (
SELECT
*
FROM #DBNAME#.DBO.#TBL2#
WHERE
RP_DATA_DT = DATEADD(D, -1, CONVERT(DATE, '2016-10-01')) AND
DAY(DATEADD(D, 1, CONVERT(DATE, RP_DATA_DT))) > 1 AND
RUN_ID = 'RC01'
), PORT AS (
SELECT
*
FROM #DBNAME#.DBO.#TBL3#
), LK_ALL AS (
SELECT
FIELDNAME
, CD
, DESCRIPTION
FROM #DBNAME#.DBO.#TBL4#
), FTP_OUT AS (
SELECT
*
FROM #DBNAME#.DBO.#TBL5#
WHERE
CURR_DT = '2016-10-01' AND
RUN_ID = 'RC01'
), DEFAULT_INDEX AS (
SELECT
IDX_FORMULA
FROM #DBNAME#.DBO.#TBL6#
WHERE
INDEX_ID = 'DEFAULT'
), STEP1 AS (
SELECT
'RC01' RUN_ID
, CONVERT(DATE, '2016-10-01') CURRENT_DT
, CONVERT(DATE, '2016-10-01') RP_DATA_DT
, CASE WHEN FTP_OUT.TXN_ID IS NULL THEN '1' ELSE '0' END ERR_FLG
, PORT.TXN_ID_R_PRODCD
, PORT.TXN_ID
, PORT.SCURITY_ID
, PORT.PRODUCT_ID
, PORT.AMRT_TYP
, PORT.CUR_PMT
, PORT.CURRENCY
, PORT.DAY_COUNT
, PORT.FIX_FLT
, PORT.FSTAMRT_DT
, PORT.FST_CPN_DT
, PORT.ISSUE_DT
, PORT.MAT_DT
, PORT.NXT_RSTDT
, PORT.ORG_PAR_BAL
, PORT.ORG_PAR_BL2
, PORT.PAY_DAY
, PORT.PMT_FLG
, PORT.PMT_FREQ
, PORT.PMT_FREQ_M
, PORT.PRV_RSTDT
, PORT.RST_FRQ
, PORT.RST_FRQ_M
, PORT.SNK_DAY
, PORT.SNK_PMT
, PORT.SNK_RT
, PORT.SNK_FRQ
, PORT.SNK_FRQ_M
, PORT.SNK_P_FLG
, PORT.TENOR
, PORT.TENOR_M
, PORT.TP_ADJ1_ID
, PORT.TP_ADJ2_ID
, PORT.TP_ADJ3_ID
, PORT.TP_ADJ4_ID
, PORT.TP_ADJ5_ID
, PORT.TP_ID
, PORT.TP_INDEX_ID
, PORT.TRANSPRICE
, PORT.ASST_LIAB
, PORT.PAR_BAL
, PORT.CUR_BK_BAL
, PORT.CUR_BK_BAL * FXSPOT.FXRATE CUR_BK_BAL_DOM
, PORT.T_CUR_BK_BAL
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE T_CUR_BK_BAL_DOM
, PORT.CUR_GRS_RT
, PORT.T_UNEARN
, PORT.T_UNEARN * FXSPOT.FXRATE T_UNEARN_DOM
, PORT.T_CURBAL
, PORT.T_CURBAL * FXSPOT.FXRATE T_CURBAL_DOM
, PORT.T_POINTAMT
, PORT.T_POINTAMT * FXSPOT.FXRATE T_POINTAMT_DOM
, PORT.T_RESTBALC
, PORT.T_RESTBALC * FXSPOT.FXRATE T_RESTBALC_DOM
, PORT.G_BANKNO
, PORT.G_CSTCTR
, PORT.G_GLACCTNO
, PORT.G_GLACCTYP
, PORT.G_GLACCTINT
, PORT.T_RTTYP
, PORT.T_PMTTYP
, PORT.T_NIDPMMD
, PORT.T_CAHSSS
, PORT.R_LOBCD
, PORT.R_BRNCHCD
, PORT.R_PRODCD
, PORT.R_TYPCD
, PORT.R_HPTYPCD
, PORT.R_NAME R_NAME
, PORT.R_CUSTCODE R_CUSTCODE
, PORT.R_PRODTYP R_PRODTYP
, PORT.R_INTPLAN R_INTPLAN
, PORT.R_DEPGRP_NAME R_DEPGRP_NAME
, PORT.R_DEPID R_DEPID
, PORT.R_MISC_IND R_MISC_IND
, PORT.R_SRCTBL R_SRCTBL
, PORT.R_RESTIND R_RESTIND
, PORT.R_SECNO R_SECNO
, PORT.R_PAIDIND R_PAIDIND
, COALESCE(FTP_OUT.TP_ORIGNAL, DEFAULT_INDEX.IDX_FORMULA) TP_ORIGNAL
, FTP_OUT.TP_ADJ1 TP_ADJ1
, FTP_OUT.TP_ADJ2 TP_ADJ2
, FTP_OUT.TP_ADJ3 TP_ADJ3
, FTP_OUT.TP_ADJ4 TP_ADJ4
, FTP_OUT.TP_ADJ5 TP_ADJ5
, COALESCE(FTP_OUT.TP_FINAL, DEFAULT_INDEX.IDX_FORMULA) TP_FINAL
, FTP_OUT.DURATION DURATION
, FTP_OUT.WAL WAL
, FTP_OUT.USER_INPUT USER_INPUT
, COALESCE(FTP_OUT.TP_MODEL, 'D') TP_MODEL
, FTP_OUT.TP_CLASS TP_CLASS
, FTP_OUT.YC_DT YC_DT
, FTP_OUT.START_DT START_DT
, FTP_OUT.END_DT END_DT
, (PORT.CUR_GRS_RT - COALESCE(FTP_OUT.TP_FINAL, DEFAULT_INDEX.IDX_FORMULA)) * PORT.ASST_LIAB TR_FINAL_SPREAD
, (PORT.CUR_GRS_RT - COALESCE(FTP_OUT.TP_ORIGNAL, DEFAULT_INDEX.IDX_FORMULA)) * PORT.ASST_LIAB TR_SPREAD
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * PORT.CUR_GRS_RT / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB TR_IE_INT
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * FTP_OUT.TP_ADJ1 / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ADJ1
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * FTP_OUT.TP_ADJ2 / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ADJ2
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * FTP_OUT.TP_ADJ3 / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ADJ3
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * FTP_OUT.TP_ADJ4 / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ADJ4
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * FTP_OUT.TP_ADJ5 / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ADJ5
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * COALESCE(FTP_OUT.TP_ORIGNAL, DEFAULT_INDEX.IDX_FORMULA) / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TP_ORG
, PORT.T_CUR_BK_BAL * FXSPOT.FXRATE * COALESCE(FTP_OUT.TP_FINAL, DEFAULT_INDEX.IDX_FORMULA) / 100.0 * PORT.DAY_COUNT_VALUE * PORT.ASST_LIAB * -1 TR_IE_TRNS
, COALESCE(FTP_OUT.TP_FINAL, DEFAULT_INDEX.IDX_FORMULA) TR_TP_FINAL
, FXSPOT.FXRATE
, DAY(CONVERT(DATE, '2016-10-01')) TR_DAYS_MTD_TODAY
FROM PORT
LEFT JOIN FTP_OUT ON PORT.TXN_ID = FTP_OUT.TXN_ID
LEFT JOIN FXSPOT ON PORT.CURRENCY = FXSPOT.CURRENCY1
LEFT JOIN DEFAULT_INDEX ON 1 = 1
), STEP2 AS (
SELECT
STEP1.*
, FTP_MTD_PRE.TXN_STATUS_FLG TXN_STATUS_FLG_PRE
, CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN '0' WHEN FTP_MTD_PRE.TXN_ID_R_PRODCD IS NOT NULL THEN '1' WHEN FTP_MTD_PRE.TXN_ID_R_PRODCD IS NULL THEN '2' END TXN_STATUS_FLG
, STEP1.T_CUR_BK_BAL_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_BAL_MTD, 0) END TR_BAL_MTD
, STEP1.T_CUR_BK_BAL_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_BAL_MTD, 0) END / DAY(CONVERT(DATE, RP_DATA_DT)) TR_AVGBAL_MTD
, STEP1.T_CURBAL_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_BAL_MTD, 0) END TR_CURBAL_MTD
, STEP1.T_CURBAL_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_BAL_MTD, 0) END / DAY(CONVERT(DATE, RP_DATA_DT)) TR_AVGCURBAL_MTD
, STEP1.TR_IE_TRNS * -1 TR_FC_TRNS
, STEP1.TR_IE_INT + STEP1.TR_IE_TRNS TR_MARGIN
, STEP1.T_POINTAMT_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_POINTAMT_MTD, 0) END TR_POINTAMT_MTD
, STEP1.T_POINTAMT_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_POINTAMT_MTD, 0) END / DAY(CONVERT(DATE, RP_DATA_DT)) TR_AVGPOINTAMT_MTD
, STEP1.T_RESTBALC_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_RESTBALC_MTD, 0) END TR_RESTBALC_MTD
, STEP1.T_RESTBALC_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_RESTBALC_MTD, 0) END / DAY(CONVERT(DATE, RP_DATA_DT)) TR_AVGRESTBALC_MTD
, STEP1.T_UNEARN_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_UNEARN_MTD, 0) END TR_UNEARN_MTD
, STEP1.T_UNEARN_DOM + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_UNEARN_MTD, 0) END / DAY(CONVERT(DATE, RP_DATA_DT)) TR_AVGUNEARN_MTD
, STEP1.TR_IE_TRNS * -1 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_FCTRNS_MTD, 0) END TR_FCTRNS_MTD
, STEP1.TR_IE_TP_ADJ1 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ADJ1_MTD, 0) END TR_IE_TP_ADJ1_MTD
, STEP1.TR_IE_TP_ADJ2 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ADJ2_MTD, 0) END TR_IE_TP_ADJ2_MTD
, STEP1.TR_IE_TP_ADJ3 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ADJ3_MTD, 0) END TR_IE_TP_ADJ3_MTD
, STEP1.TR_IE_TP_ADJ4 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ADJ4_MTD, 0) END TR_IE_TP_ADJ4_MTD
, STEP1.TR_IE_TP_ADJ5 + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ADJ5_MTD, 0) END TR_IE_TP_ADJ5_MTD
, STEP1.TR_IE_TP_ORG + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IE_TP_ORG_MTD, 0) END TR_IE_TP_ORG_MTD
, STEP1.TR_IE_INT + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IEINT_MTD, 0) END TR_IEINT_MTD
, STEP1.TR_IE_TRNS + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_IETRNS_MTD, 0) END TR_IETRNS_MTD
, STEP1.TR_IE_INT + STEP1.TR_IE_TRNS + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.TR_MARGIN_MTD, 0) END TR_MARGIN_MTD
, STEP1.CUR_GRS_RT * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB W_CUR_GRS_RT
, (STEP1.CUR_GRS_RT * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB) + CASE WHEN TR_DAYS_MTD_TODAY = 1 THEN 0 ELSE ISNULL(FTP_MTD_PRE.W_CUR_GRS_RT_MTD, 0) END W_CUR_GRS_RT_MTD
, STEP1.TP_ADJ1 * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TP_ADJ1
, STEP1.TP_ADJ2 * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TP_ADJ2
, STEP1.TP_ADJ3 * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TP_ADJ3
, STEP1.TP_ADJ4 * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TP_ADJ4
, STEP1.TP_ADJ5 * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TP_ADJ5
, STEP1.TR_FINAL_SPREAD * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TR_FINAL_SPREAD
, STEP1.TP_FINAL * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TR_TP_FINAL
, STEP1.TP_ORIGNAL * STEP1.T_CUR_BK_BAL_DOM * STEP1.ASST_LIAB * -1 W_TR_TP_ORIGNAL
, FTP_MTD_PRE.W_CUR_GRS_RT_MTD P_W_CUR_GRS_RT_MTD
, FTP_MTD_PRE.W_TP_ADJ1_MTD P_W_TP_ADJ1_MTD
, FTP_MTD_PRE.W_TP_ADJ2_MTD P_W_TP_ADJ2_MTD
, FTP_MTD_PRE.W_TP_ADJ3_MTD P_W_TP_ADJ3_MTD
, FTP_MTD_PRE.W_TP_ADJ4_MTD P_W_TP_ADJ4_MTD
, FTP_MTD_PRE.W_TP_ADJ5_MTD P_W_TP_ADJ5_MTD
, FTP_MTD_PRE.W_TR_FINAL_SPREAD_MTD P_W_TR_FINAL_SPREAD_MTD
, FTP_MTD_PRE.W_TR_TP_FINAL_MTD P_W_TR_TP_FINAL_MTD
, FTP_MTD_PRE.W_TR_TP_ORIGNAL_MTD P_W_TR_TP_ORIGNAL_MTD
FROM STEP1
LEFT JOIN FTP_MTD_PRE ON STEP1.TXN_ID_R_PRODCD = FTP_MTD_PRE.TXN_ID_R_PRODCD
)
INSERT INTO R_PBB_FTP_V1225.DBO.FTP_MTD_MC (
RP_DATA_DT
, TXN_ID_R_PRODCD
, TXN_STATUS_FLG
, TXN_STATUS_PREV
, ERR_FLG
, TXN_ID
, SCURITY_ID
, PRODUCT_ID
, AMRT_TYP
, ASST_LIAB
, CUR_BK_BAL
, T_CUR_BK_BAL
, CUR_GRS_RT
, CUR_PMT
, CURRENCY
, DAY_COUNT
, FIX_FLT
, FSTAMRT_DT
, FST_CPN_DT
, ISSUE_DT
, MAT_DT
, NXT_RSTDT
, ORG_PAR_BAL
, ORG_PAR_BL2
, PAY_DAY
, PMT_FLG
, PMT_FREQ
, PMT_FREQ_M
, PRV_RSTDT
, RST_FRQ
, RST_FRQ_M
, SNK_DAY
, SNK_FRQ
, SNK_FRQ_M
, SNK_P_FLG
, SNK_PMT
, SNK_RT
, TENOR
, TENOR_M
, TP_ADJ1_ID
, TP_ADJ2_ID
, TP_ADJ3_ID
, TP_ADJ4_ID
, TP_ADJ5_ID
, TP_ID
, TP_INDEX_ID
, TRANSPRICE
, RUN_ID
, CURRENT_DT
, TP_ORIGNAL
, TP_ADJ1
, TP_ADJ2
, TP_ADJ3
, TP_ADJ4
, TP_ADJ5
, TP_FINAL
, DURATION
, WAL
, USER_INPUT
, TP_MODEL
, TP_CLASS
, YC_DT
, START_DT
, END_DT
, FXRATE
, CUR_BK_BAL_DOM
, T_CUR_BK_BAL_DOM
, TR_SPREAD
, TR_FINAL_SPREAD
, TR_TP_FINAL
, TR_IE_INT
, TR_IE_TP_ORG
, TR_IE_TP_ADJ1
, TR_IE_TP_ADJ2
, TR_IE_TP_ADJ3
, TR_IE_TP_ADJ4
, TR_IE_TP_ADJ5
, TR_IE_TRNS
, TR_MARGIN
, TR_FC_TRNS
, TR_DAYS_MTD
, TR_BAL_MTD
, TR_AVGBAL_MTD
, TR_IEINT_MTD
, TR_IE_TP_ORG_MTD
, TR_IE_TP_ADJ1_MTD
, TR_IE_TP_ADJ2_MTD
, TR_IE_TP_ADJ3_MTD
, TR_IE_TP_ADJ4_MTD
, TR_IE_TP_ADJ5_MTD
, TR_IETRNS_MTD
, TR_MARGIN_MTD
, TR_FCTRNS_MTD
, W_CUR_GRS_RT
, W_TR_TP_ORIGNAL
, W_TP_ADJ1
, W_TP_ADJ2
, W_TP_ADJ3
, W_TP_ADJ4
, W_TP_ADJ5
, W_TR_FINAL_SPREAD
, W_TR_TP_FINAL
, W_CUR_GRS_RT_MTD
, W_TR_TP_ORIGNAL_MTD
, W_TP_ADJ1_MTD
, W_TP_ADJ2_MTD
, W_TP_ADJ3_MTD
, W_TP_ADJ4_MTD
, W_TP_ADJ5_MTD
, W_TR_FINAL_SPREAD_MTD
, W_TR_TP_FINAL_MTD
, AVG_CUR_GRS_RT
, AVG_TR_TP_ORIGNAL
, AVG_TP_ADJ1
, AVG_TP_ADJ2
, AVG_TP_ADJ3
, AVG_TP_ADJ4
, AVG_TP_ADJ5
, AVG_TR_FINAL_SPREAD
, AVG_TR_TP_FINAL
, G_BANKNO
, G_CSTCTR
, G_GLACCTNO
, G_GLACCTYP
, G_GLACCTINT
, T_RTTYP
, T_PMTTYP
, T_NIDPMMD
, T_CAHSSS
, R_LOBCD
, R_BRNCHCD
, R_PRODCD
, R_TYPCD
, R_HPTYPCD
, DSC_G_BANKNO
, DSC_G_CSTCTR
, DSC_G_GLACCTNO
, DSC_G_GLACCTYP
, DSC_G_GLACCTINT
, DSC_T_RTTYP
, DSC_T_PMTTYP
, DSC_T_NIDPMMD
, DSC_T_CAHSSS
, DSC_R_LOBCD
, DSC_R_BRNCHCD
, DSC_R_PRODCD
, DSC_R_TYPCD
, DSC_R_HPTYPCD
, R_NAME
, R_CUSTCODE
, R_PRODTYP
, R_INTPLAN
, R_DEPGRP_NAME
, R_DEPID
, R_MISC_IND
, R_SRCTBL
, R_RESTIND
, R_SECNO
, R_PAIDIND
, T_UNEARN
, T_UNEARN_DOM
, TR_UNEARN_MTD
, TR_AVGUNEARN_MTD
, T_CURBAL
, T_CURBAL_DOM
, TR_CURBAL_MTD
, TR_AVGCURBAL_MTD
, T_POINTAMT
, T_POINTAMT_DOM
, TR_POINTAMT_MTD
, TR_AVGPOINTAMT_MTD
, T_RESTBALC
, T_RESTBALC_DOM
, TR_RESTBALC_MTD
, TR_AVGRESTBALC_MTD
, CURR_DT
, R_PROCESS_DT
)
SELECT
STEP2.RP_DATA_DT
, STEP2.TXN_ID_R_PRODCD
, STEP2.TXN_STATUS_FLG
, STEP2.TXN_STATUS_FLG_PRE
, STEP2.ERR_FLG
, STEP2.TXN_ID
, STEP2.SCURITY_ID
, STEP2.PRODUCT_ID
, STEP2.AMRT_TYP
, STEP2.ASST_LIAB
, STEP2.CUR_BK_BAL
, STEP2.T_CUR_BK_BAL
, STEP2.CUR_GRS_RT
, STEP2.CUR_PMT
, STEP2.CURRENCY
, STEP2.DAY_COUNT
, STEP2.FIX_FLT
, STEP2.FSTAMRT_DT
, STEP2.FST_CPN_DT
, STEP2.ISSUE_DT
, STEP2.MAT_DT
, STEP2.NXT_RSTDT
, STEP2.ORG_PAR_BAL
, STEP2.ORG_PAR_BL2
, STEP2.PAY_DAY
, STEP2.PMT_FLG
, STEP2.PMT_FREQ
, STEP2.PMT_FREQ_M
, STEP2.PRV_RSTDT
, STEP2.RST_FRQ
, STEP2.RST_FRQ_M
, STEP2.SNK_DAY
, STEP2.SNK_FRQ
, STEP2.SNK_FRQ_M
, STEP2.SNK_P_FLG
, STEP2.SNK_PMT
, STEP2.SNK_RT
, STEP2.TENOR
, STEP2.TENOR_M
, STEP2.TP_ADJ1_ID
, STEP2.TP_ADJ2_ID
, STEP2.TP_ADJ3_ID
, STEP2.TP_ADJ4_ID
, STEP2.TP_ADJ5_ID
, STEP2.TP_ID
, STEP2.TP_INDEX_ID
, STEP2.TRANSPRICE
, STEP2.RUN_ID
, STEP2.CURRENT_DT
, STEP2.TP_ORIGNAL
, STEP2.TP_ADJ1
, STEP2.TP_ADJ2
, STEP2.TP_ADJ3
, STEP2.TP_ADJ4
, STEP2.TP_ADJ5
, STEP2.TP_FINAL
, STEP2.DURATION
, STEP2.WAL
, CASE WHEN STEP2.USER_INPUT = '' THEN NULL ELSE STEP2.USER_INPUT END USER_INPUT
, CASE WHEN STEP2.TP_MODEL = '' THEN NULL ELSE STEP2.TP_MODEL END TP_MODEL
, STEP2.TP_CLASS
, STEP2.YC_DT
, STEP2.START_DT
, STEP2.END_DT
, STEP2.FXRATE
, STEP2.CUR_BK_BAL_DOM
, STEP2.T_CUR_BK_BAL_DOM
, STEP2.TR_SPREAD
, STEP2.TR_FINAL_SPREAD
, STEP2.TR_TP_FINAL
, STEP2.TR_IE_INT
, STEP2.TR_IE_TP_ORG
, STEP2.TR_IE_TP_ADJ1
, STEP2.TR_IE_TP_ADJ2
, STEP2.TR_IE_TP_ADJ3
, STEP2.TR_IE_TP_ADJ4
, STEP2.TR_IE_TP_ADJ5
, STEP2.TR_IE_TRNS
, STEP2.TR_MARGIN
, STEP2.TR_FC_TRNS
, STEP2.TR_DAYS_MTD_TODAY
, STEP2.TR_BAL_MTD
, STEP2.TR_AVGBAL_MTD
, STEP2.TR_IEINT_MTD
, STEP2.TR_IE_TP_ORG_MTD
, STEP2.TR_IE_TP_ADJ1_MTD
, STEP2.TR_IE_TP_ADJ2_MTD
, STEP2.TR_IE_TP_ADJ3_MTD
, STEP2.TR_IE_TP_ADJ4_MTD
, STEP2.TR_IE_TP_ADJ5_MTD
, STEP2.TR_IETRNS_MTD
, STEP2.TR_MARGIN_MTD
, STEP2.TR_FCTRNS_MTD
, STEP2.W_CUR_GRS_RT
, STEP2.W_TR_TP_ORIGNAL
, STEP2.W_TP_ADJ1
, STEP2.W_TP_ADJ2
, STEP2.W_TP_ADJ3
, STEP2.W_TP_ADJ4
, STEP2.W_TP_ADJ5
, STEP2.W_TR_FINAL_SPREAD
, STEP2.W_TR_TP_FINAL
, STEP2.W_CUR_GRS_RT_MTD
, STEP2.W_TR_TP_ORIGNAL + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_TP_ORIGNAL_MTD END W_TR_TP_ORIGNAL_MTD
, STEP2.W_TP_ADJ1 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ1_MTD END W_TP_ADJ1_MTD
, STEP2.W_TP_ADJ2 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ2_MTD END W_TP_ADJ2_MTD
, STEP2.W_TP_ADJ3 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ3_MTD END W_TP_ADJ3_MTD
, STEP2.W_TP_ADJ4 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ4_MTD END W_TP_ADJ4_MTD
, STEP2.W_TP_ADJ5 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ5_MTD END W_TP_ADJ5_MTD
, STEP2.W_TR_FINAL_SPREAD + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_FINAL_SPREAD_MTD END W_TR_FINAL_SPREAD_MTD
, STEP2.W_TR_TP_FINAL + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_TP_FINAL_MTD END W_TR_TP_FINAL_MTD
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE STEP2.W_CUR_GRS_RT_MTD / STEP2.TR_BAL_MTD END AVG_CUR_GRS_RT
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TR_TP_ORIGNAL + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_TP_ORIGNAL_MTD END) / STEP2.TR_BAL_MTD END AVG_TR_TP_ORIGNAL
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TP_ADJ1 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ1_MTD END) / STEP2.TR_BAL_MTD END AVG_TP_ADJ1
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TP_ADJ2 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ2_MTD END) / STEP2.TR_BAL_MTD END AVG_TP_ADJ2
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TP_ADJ3 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ3_MTD END) / STEP2.TR_BAL_MTD END AVG_TP_ADJ3
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TP_ADJ4 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ4_MTD END) / STEP2.TR_BAL_MTD END AVG_TP_ADJ4
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TP_ADJ5 + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TP_ADJ5_MTD END) / STEP2.TR_BAL_MTD END AVG_TP_ADJ5
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TR_FINAL_SPREAD + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_FINAL_SPREAD_MTD END) / STEP2.TR_BAL_MTD END AVG_TR_FINAL_SPREAD
, CASE WHEN STEP2.TR_BAL_MTD = 0 THEN 0 ELSE (STEP2.W_TR_TP_FINAL + CASE WHEN STEP2.TXN_STATUS_FLG = 0 THEN 0 ELSE STEP2.P_W_TR_TP_FINAL_MTD END) / STEP2.TR_BAL_MTD END AVG_TR_TP_FINAL
, STEP2.G_BANKNO
, STEP2.G_CSTCTR
, STEP2.G_GLACCTNO
, STEP2.G_GLACCTYP
, STEP2.G_GLACCTINT
, STEP2.T_RTTYP
, STEP2.T_PMTTYP
, STEP2.T_NIDPMMD
, STEP2.T_CAHSSS
, STEP2.R_LOBCD
, STEP2.R_BRNCHCD
, STEP2.R_PRODCD
, STEP2.R_TYPCD
, STEP2.R_HPTYPCD
, D1.DESCRIPTION DSC_G_BANKNO
, D2.DESCRIPTION DSC_G_CSTCTR
, D3.DESCRIPTION DSC_G_GLACCTNO
, D4.DESCRIPTION DSC_G_GLACCTYP
, D5.DESCRIPTION DSC_G_GLACCTINT
, D6.DESCRIPTION DSC_T_RTTYP
, D7.DESCRIPTION DSC_T_PMTTYP
, D8.DESCRIPTION DSC_T_NIDPMMD
, D9.DESCRIPTION DSC_T_CAHSSS
, D10.DESCRIPTION DSC_R_LOBCD
, D11.DESCRIPTION DSC_R_BRNCHCD
, D12.DESCRIPTION DSC_R_PRODCD
, D13.DESCRIPTION DSC_R_TYPCD
, D14.DESCRIPTION DSC_R_HPTYPCD
, STEP2.R_NAME
, STEP2.R_CUSTCODE
, STEP2.R_PRODTYP
, STEP2.R_INTPLAN
, STEP2.R_DEPGRP_NAME
, STEP2.R_DEPID
, STEP2.R_MISC_IND
, STEP2.R_SRCTBL
, STEP2.R_RESTIND
, STEP2.R_SECNO
, STEP2.R_PAIDIND
, STEP2.T_UNEARN
, STEP2.T_UNEARN_DOM
, STEP2.TR_UNEARN_MTD
, STEP2.TR_AVGUNEARN_MTD
, STEP2.T_CURBAL
, STEP2.T_CURBAL_DOM
, STEP2.TR_CURBAL_MTD
, STEP2.TR_AVGCURBAL_MTD
, STEP2.T_POINTAMT
, STEP2.T_POINTAMT_DOM
, STEP2.TR_POINTAMT_MTD
, STEP2.TR_AVGPOINTAMT_MTD
, STEP2.T_RESTBALC
, STEP2.T_RESTBALC_DOM
, STEP2.TR_RESTBALC_MTD
, STEP2.TR_AVGRESTBALC_MTD
, RP_DATA_DT CURR_DT
, CONVERT(DATE, GETDATE()) R_PROCESS_DT
FROM STEP2
LEFT JOIN LK_ALL D1 ON D1.FIELDNAME = 'G_BANKNO' AND STEP2.G_BANKNO = D1.CD
LEFT JOIN LK_ALL D2 ON D2.FIELDNAME = 'G_CSTCTR' AND STEP2.G_CSTCTR = D2.CD
LEFT JOIN LK_ALL D3 ON D3.FIELDNAME = 'G_GLACCTNO' AND STEP2.G_GLACCTNO = D3.CD
LEFT JOIN LK_ALL D4 ON D4.FIELDNAME = 'G_GLACCTYP' AND STEP2.G_GLACCTYP = D4.CD
LEFT JOIN LK_ALL D5 ON D5.FIELDNAME = 'G_GLACCTINT' AND STEP2.G_GLACCTINT = D5.CD
LEFT JOIN LK_ALL D6 ON D6.FIELDNAME = 'T_RTTYP' AND STEP2.T_RTTYP = D6.CD
LEFT JOIN LK_ALL D7 ON D7.FIELDNAME = 'T_PMTTYP' AND STEP2.T_PMTTYP = D7.CD
LEFT JOIN LK_ALL D8 ON D8.FIELDNAME = 'T_NIDPMMD' AND STEP2.T_NIDPMMD = D8.CD
LEFT JOIN LK_ALL D9 ON D9.FIELDNAME = 'T_CAHSSS' AND STEP2.T_CAHSSS = D9.CD
LEFT JOIN LK_ALL D10 ON D10.FIELDNAME = 'R_LOBCD' AND STEP2.R_LOBCD = D10.CD
LEFT JOIN LK_ALL D11 ON D11.FIELDNAME = 'R_BRNCHCD' AND STEP2.R_BRNCHCD = D11.CD
LEFT JOIN LK_ALL D12 ON D12.FIELDNAME = 'R_PRODCD' AND STEP2.R_PRODCD = D12.CD
LEFT JOIN LK_ALL D13 ON D13.FIELDNAME = 'R_TYPCD' AND STEP2.R_TYPCD = D13.CD
LEFT JOIN LK_ALL D14 ON D14.FIELDNAME = 'R_HPTYPCD' AND STEP2.R_HPTYPCD = D14.CD
UNION ALL
SELECT
'2016-10-01' RP_DATA_DT
, FTP_MTD_PRE.TXN_ID_R_PRODCD
, '3' TXN_STATUS_FLG
, FTP_MTD_PRE.TXN_STATUS_FLG
, FTP_MTD_PRE.ERR_FLG
, FTP_MTD_PRE.TXN_ID
, FTP_MTD_PRE.SCURITY_ID
, FTP_MTD_PRE.PRODUCT_ID
, FTP_MTD_PRE.AMRT_TYP
, FTP_MTD_PRE.ASST_LIAB
, 0 CUR_BK_BAL
, 0 T_CUR_BK_BAL
, FTP_MTD_PRE.CUR_GRS_RT
, FTP_MTD_PRE.CUR_PMT
, FTP_MTD_PRE.CURRENCY
, FTP_MTD_PRE.DAY_COUNT
, FTP_MTD_PRE.FIX_FLT
, FTP_MTD_PRE.FSTAMRT_DT
, FTP_MTD_PRE.FST_CPN_DT
, FTP_MTD_PRE.ISSUE_DT
, FTP_MTD_PRE.MAT_DT
, FTP_MTD_PRE.NXT_RSTDT
, FTP_MTD_PRE.ORG_PAR_BAL
, FTP_MTD_PRE.ORG_PAR_BL2
, FTP_MTD_PRE.PAY_DAY
, FTP_MTD_PRE.PMT_FLG
, FTP_MTD_PRE.PMT_FREQ
, FTP_MTD_PRE.PMT_FREQ_M
, FTP_MTD_PRE.PRV_RSTDT
, FTP_MTD_PRE.RST_FRQ
, FTP_MTD_PRE.RST_FRQ_M
, FTP_MTD_PRE.SNK_DAY
, FTP_MTD_PRE.SNK_FRQ
, FTP_MTD_PRE.SNK_FRQ_M
, FTP_MTD_PRE.SNK_P_FLG
, FTP_MTD_PRE.SNK_PMT
, FTP_MTD_PRE.SNK_RT
, FTP_MTD_PRE.TENOR
, FTP_MTD_PRE.TENOR_M
, FTP_MTD_PRE.TP_ADJ1_ID
, FTP_MTD_PRE.TP_ADJ2_ID
, FTP_MTD_PRE.TP_ADJ3_ID
, FTP_MTD_PRE.TP_ADJ4_ID
, FTP_MTD_PRE.TP_ADJ5_ID
, FTP_MTD_PRE.TP_ID
, FTP_MTD_PRE.TP_INDEX_ID
, FTP_MTD_PRE.TRANSPRICE
, FTP_MTD_PRE.RUN_ID
, FTP_MTD_PRE.CURRENT_DT
, FTP_MTD_PRE.TP_ORIGNAL
, FTP_MTD_PRE.TP_ADJ1
, FTP_MTD_PRE.TP_ADJ2
, FTP_MTD_PRE.TP_ADJ3
, FTP_MTD_PRE.TP_ADJ4
, FTP_MTD_PRE.TP_ADJ5
, FTP_MTD_PRE.TP_FINAL
, FTP_MTD_PRE.DURATION
, FTP_MTD_PRE.WAL
, FTP_MTD_PRE.USER_INPUT
, FTP_MTD_PRE.TP_MODEL
, FTP_MTD_PRE.TP_CLASS
, FTP_MTD_PRE.YC_DT
, FTP_MTD_PRE.START_DT
, FTP_MTD_PRE.END_DT
, FTP_MTD_PRE.FXRATE
, 0 CUR_BK_BAL_DOM
, 0 T_CUR_BK_BAL_DOM
, FTP_MTD_PRE.TR_SPREAD
, FTP_MTD_PRE.TR_FINAL_SPREAD
, FTP_MTD_PRE.TR_TP_FINAL
, FTP_MTD_PRE.TR_IE_INT
, FTP_MTD_PRE.TR_IE_TP_ORG
, FTP_MTD_PRE.TR_IE_TP_ADJ1
, FTP_MTD_PRE.TR_IE_TP_ADJ2
, FTP_MTD_PRE.TR_IE_TP_ADJ3
, FTP_MTD_PRE.TR_IE_TP_ADJ4
, FTP_MTD_PRE.TR_IE_TP_ADJ5
, FTP_MTD_PRE.TR_IE_TRNS
, FTP_MTD_PRE.TR_MARGIN
, FTP_MTD_PRE.TR_FC_TRNS
, FTP_MTD_PRE.TR_DAYS_MTD + 1 TR_DAYS_MTD
, FTP_MTD_PRE.TR_BAL_MTD
, FTP_MTD_PRE.TR_AVGBAL_MTD
, FTP_MTD_PRE.TR_IEINT_MTD
, FTP_MTD_PRE.TR_IE_TP_ORG_MTD
, FTP_MTD_PRE.TR_IE_TP_ADJ1_MTD
, FTP_MTD_PRE.TR_IE_TP_ADJ2_MTD
, FTP_MTD_PRE.TR_IE_TP_ADJ3_MTD
, FTP_MTD_PRE.TR_IE_TP_ADJ4_MTD
, FTP_MTD_PRE.TR_IE_TP_ADJ5_MTD
, FTP_MTD_PRE.TR_IETRNS_MTD
, FTP_MTD_PRE.TR_MARGIN_MTD
, FTP_MTD_PRE.TR_FCTRNS_MTD
, FTP_MTD_PRE.W_CUR_GRS_RT
, FTP_MTD_PRE.W_TR_TP_ORIGNAL
, FTP_MTD_PRE.W_TP_ADJ1
, FTP_MTD_PRE.W_TP_ADJ2
, FTP_MTD_PRE.W_TP_ADJ3
, FTP_MTD_PRE.W_TP_ADJ4
, FTP_MTD_PRE.W_TP_ADJ5
, FTP_MTD_PRE.W_TR_FINAL_SPREAD
, FTP_MTD_PRE.W_TR_TP_FINAL
, FTP_MTD_PRE.W_CUR_GRS_RT_MTD
, FTP_MTD_PRE.W_TR_TP_ORIGNAL_MTD
, FTP_MTD_PRE.W_TP_ADJ1_MTD
, FTP_MTD_PRE.W_TP_ADJ2_MTD
, FTP_MTD_PRE.W_TP_ADJ3_MTD
, FTP_MTD_PRE.W_TP_ADJ4_MTD
, FTP_MTD_PRE.W_TP_ADJ5_MTD
, FTP_MTD_PRE.W_TR_FINAL_SPREAD_MTD
, FTP_MTD_PRE.W_TR_TP_FINAL_MTD
, FTP_MTD_PRE.AVG_CUR_GRS_RT
, FTP_MTD_PRE.AVG_TR_TP_ORIGNAL
, FTP_MTD_PRE.AVG_TP_ADJ1
, FTP_MTD_PRE.AVG_TP_ADJ2
, FTP_MTD_PRE.AVG_TP_ADJ3
, FTP_MTD_PRE.AVG_TP_ADJ4
, FTP_MTD_PRE.AVG_TP_ADJ5
, FTP_MTD_PRE.AVG_TR_FINAL_SPREAD
, FTP_MTD_PRE.AVG_TR_TP_FINAL
, FTP_MTD_PRE.G_BANKNO
, FTP_MTD_PRE.G_CSTCTR
, FTP_MTD_PRE.G_GLACCTNO
, FTP_MTD_PRE.G_GLACCTYP
, FTP_MTD_PRE.G_GLACCTINT
, FTP_MTD_PRE.T_RTTYP
, FTP_MTD_PRE.T_PMTTYP
, FTP_MTD_PRE.T_NIDPMMD
, FTP_MTD_PRE.T_CAHSSS
, FTP_MTD_PRE.R_LOBCD
, FTP_MTD_PRE.R_BRNCHCD
, FTP_MTD_PRE.R_PRODCD
, FTP_MTD_PRE.R_TYPCD
, FTP_MTD_PRE.R_HPTYPCD
, FTP_MTD_PRE.DSC_G_BANKNO
, FTP_MTD_PRE.DSC_G_CSTCTR
, FTP_MTD_PRE.DSC_G_GLACCTNO
, FTP_MTD_PRE.DSC_G_GLACCTYP
, FTP_MTD_PRE.DSC_G_GLACCTINT
, FTP_MTD_PRE.DSC_T_RTTYP
, FTP_MTD_PRE.DSC_T_PMTTYP
, FTP_MTD_PRE.DSC_T_NIDPMMD
, FTP_MTD_PRE.DSC_T_CAHSSS
, FTP_MTD_PRE.DSC_R_LOBCD
, FTP_MTD_PRE.DSC_R_BRNCHCD
, FTP_MTD_PRE.DSC_R_PRODCD
, FTP_MTD_PRE.DSC_R_TYPCD
, FTP_MTD_PRE.DSC_R_HPTYPCD
, FTP_MTD_PRE.R_NAME
, FTP_MTD_PRE.R_CUSTCODE
, FTP_MTD_PRE.R_PRODTYP
, FTP_MTD_PRE.R_INTPLAN
, FTP_MTD_PRE.R_DEPGRP_NAME
, FTP_MTD_PRE.R_DEPID
, FTP_MTD_PRE.R_MISC_IND
, FTP_MTD_PRE.R_SRCTBL
, FTP_MTD_PRE.R_RESTIND
, FTP_MTD_PRE.R_SECNO
, FTP_MTD_PRE.R_PAIDIND
, 0 T_UNEARN
, 0 T_UNEARN_DOM
, FTP_MTD_PRE.TR_UNEARN_MTD
, FTP_MTD_PRE.TR_AVGUNEARN_MTD
, 0 T_CURBAL
, 0 T_CURBAL_DOM
, FTP_MTD_PRE.TR_CURBAL_MTD
, FTP_MTD_PRE.TR_AVGCURBAL_MTD
, 0 T_POINTAMT
, 0 T_POINTAMT_DOM
, FTP_MTD_PRE.TR_POINTAMT_MTD
, FTP_MTD_PRE.TR_AVGPOINTAMT_MTD
, 0 T_RESTBALC
, 0 T_RESTBALC_DOM
, FTP_MTD_PRE.TR_RESTBALC_MTD
, FTP_MTD_PRE.TR_AVGRESTBALC_MTD
, '2016-10-01' CURR_DT
, CONVERT(DATE, GETDATE()) R_PROCESS_DT
FROM FTP_MTD_PRE
WHERE NOT EXISTS (SELECT 1 FROM PORT WHERE FTP_MTD_PRE.TXN_ID_R_PRODCD = PORT.TXN_ID_R_PRODCD)
Soool is copy of data in temp db .
Query have cte used to create new cte so execution logic is getting complicated to solve the problem sql server need to create interim result set the same is visible in query plan as spool.
https://technet.microsoft.com/en-us/library/ms181032(v=sql.105).aspx