Here's the coding I have done thus far:
[code]IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C
IF OBJECT_ID('TEMPDB..#TMP3C') IS NOT NULL DROP TABLE #TMP3C
SELECT PRIN_MON, AGNT_MON, SUB_ACCT_NO_MON, TRAN_CDE_MON, TRAN_AMT_MON, TRAN_DTE_MON, OP_ID_MON,
TERM_ID_MON, ITM_TYP_MON, ENTRY_REFNO_MON
INTO #TMP1C
FROM Vantage.dbo.MON_TRAN_BASE (NOLOCK)
WHERE ENTRY_REFNO_MON LIKE ('DEPO%')
AND TRAN_DTE_MON BETWEEN '2015-04-01' AND GETDATE()
AND PRIN_MON IN (6000,7500)
--AND SUB_ACCT_NO_MON = '8495600011049428'
--SELECT * FROM #TMP1C
SELECT A.PRIN_MON, A.AGNT_MON, A.SUB_ACCT_NO_MON AS DEP_ACCT_NO,
A.TRAN_CDE_MON AS DEPOSIT_CODE, A.ENTRY_REFNO_MON AS REFERENCE,
A.TRAN_AMT_MON AS DEPOSIT_AMT, A.TRAN_DTE_MON AS DEPOSIT_DTE,
A.OP_ID_MON AS DEPOSIT_OPID, A.TERM_ID_MON AS DEPOSIT_TERM,
A.ITM_TYP_MON AS ITEM_TYPE, M.PRIN_MON AS REV_PRIN, M.AGNT_MON AS REV_AGNT,
M.SUB_ACCT_NO_MON AS REV_ACCT_NO, M.TRAN_CDE_MON AS REV_CODE,
M.TRAN_DTE_MON AS REV_DATE, M.ENTRY_REFNO_MON AS REFERENCE2,
M.TRAN_AMT_MON AS REV_AMOUNT, M.OP_ID_MON AS REV_OPID,
M.TERM_ID_MON AS REV_TERM, M.ITM_TYP_MON AS ITEM_TYPE2
INTO #TMP2C
FROM #TMP1C AS A (NOLOCK) LEFT JOIN Vantage.dbo.MON_TRAN_BASE AS M (NOLOCK)
ON A.PRIN_MON = M.PRIN_MON
AND A.AGNT_MON = M.AGNT_MON
AND A.SUB_ACCT_NO_MON = M.SUB_ACCT_NO_MON
WHERE M.ENTRY_REFNO_MON LIKE ('DEPREV%')
AND M.TRAN_DTE_MON BETWEEN '2015-04-01' AND GETDATE()
AND M.TRAN_AMT_MON < 0
AND M.PRIN_MON IN (6000,7500)
--SELECT * FROM #TMP2C
SELECT DISTINCT A.PRIN_MON, A.AGNT_MON, A.DEP_ACCT_NO,
C.RES_NAME_SBB AS CUSTOMER_NAME,
CASE WHEN C.EXT_STAT_SBB = ' ' THEN 'ACTIVE'
ELSE ' ' END AS CUSTOMER_STATUS,
A.DEPOSIT_CODE,
A.REFERENCE,
A.DEPOSIT_AMT,
A.DEPOSIT_DTE,
A.DEPOSIT_OPID,
A.DEPOSIT_TERM,
H.CREATE_DTE_OHI AS DEP_CREATE_DTE,
H.ORDER_NO_OHI AS DEP_ORD_NO,
H.LS_CHG_DTE_OHI AS DEP_CHG_DTE,
A.ITEM_TYPE,
A.REV_PRIN,
A.REV_AGNT,
A.REV_CODE,
A.REV_DATE,
A.REFERENCE2,
A.REV_AMOUNT,
A.REV_OPID,
A.REV_TERM,
A.ITEM_TYPE2,
H.CONNECT_DTE_OHI,
H2.CREATE_DTE_OHI AS REV_CREATE_DTE,
H2.ORDER_NO_OHI as REV_ORD_NO,
H2.LS_CHG_DTE_OHI AS REV_CHG_DTE,
-- H.SERV_CDE_OHI,
H.BEF_QTY_OHI,
H.AFT_QTY_OHI
INTO #TMP3C
FROM #TMP2C AS A (NOLOCK) LEFT JOIN Vantage.dbo.OHI_HIST_ITEM AS H (NOLOCK)
ON A.PRIN_MON = H.PRIN_OHI
AND A.AGNT_MON = H.AGNT_OHI
AND A.DEP_ACCT_NO = H.SUB_ACCT_NO_OHI
AND H.ACCT_STAGE_OHI = 'C' AND H.ITEM_STATUS_OHI IN ('C','O','P','B')
LEFT JOIN Vantage.dbo.OHI_HIST_ITEM AS H2 (NOLOCK)
ON A.REV_PRIN = H2.PRIN_OHI
AND A.REV_AGNT = H2.AGNT_OHI
AND A.REV_ACCT_NO = H2.SUB_ACCT_NO_OHI
AND H2.ACCT_STAGE_OHI = 'C' AND H2.ITEM_STATUS_OHI IN ('C','O','P','B')
LEFT JOIN Vantage.dbo.SBB_BASE AS C (NOLOCK)
ON H.PRIN_OHI = C.PRIN_SBB
AND H.AGNT_OHI = C.AGNT_SBB
AND H.SUB_ACCT_NO_OHI = C.SUB_ACCT_NO_SBB
--WHERE A.SUB_ACCT_NO_MON = '8495752510982979'
WHERE DATEDIFF(D,A.DEPOSIT_DTE,A.REV_DATE) <= 90
AND C.EXT_STAT_SBB = ' '
AND H.CONNECT_DTE_OHI = (SELECT MAX(CONNECT_DTE_OHI) AS CONNECT_DTE_OHI FROM Vantage.dbo.OHI_HIST_ITEM as B (NOLOCK)
WHERE A.DEP_ACCT_NO = B.SUB_ACCT_NO_OHI)
GROUP BY A.PRIN_MON, A.AGNT_MON, A.DEP_ACCT_NO, C.RES_NAME_SBB, C.EXT_STAT_SBB,
A.DEPOSIT_CODE,
A.REFERENCE,
A.DEPOSIT_AMT,
A.DEPOSIT_DTE,
A.DEPOSIT_OPID,
A.DEPOSIT_TERM,
H.CREATE_DTE_OHI,
H.LS_CHG_DTE_OHI,
H.ORDER_NO_OHI,
A.ITEM_TYPE,
A.REV_PRIN,
A.REV_AGNT,
A.REV_CODE,
A.REV_DATE,
A.REFERENCE2,
A.REV_AMOUNT,
A.REV_OPID,
A.REV_TERM,
A.ITEM_TYPE2,
H.CONNECT_DTE_OHI, H2.CREATE_DTE_OHI, H2.ORDER_NO_OHI, H2.LS_CHG_DTE_OHI,
--H.SERV_CDE_OHI,
H.BEF_QTY_OHI,
H.AFT_QTY_OHI
--HAVING H.AFT_QTY_OHI - H.BEF_QTY_OHI > 0
SELECT DISTINCT A.PRIN_MON, A.AGNT_MON, A.DEP_ACCT_NO,
A.CUSTOMER_NAME,
A.CUSTOMER_STATUS,
A.DEPOSIT_CODE,
A.REFERENCE,
A.DEPOSIT_AMT,
A.DEPOSIT_DTE,
A.DEPOSIT_OPID,
A.DEPOSIT_TERM,
--A.DEP_CREATE_DTE,
A.DEP_ORD_NO,
--A.DEP_CHG_DTE,
A.ITEM_TYPE,
A.REV_PRIN,
A.REV_AGNT,
A.REV_CODE,
A.REV_DATE,
A.REFERENCE2,
A.REV_AMOUNT,
A.REV_OPID,
A.REV_TERM,
A.ITEM_TYPE2,
A.CONNECT_DTE_OHI,
--A.REV_CREATE_DTE,
A.REV_ORD_NO--,
--A.REV_CHG_DTE
FROM #TMP3C AS A (NOLOCK)
[/code]