DROP TABLE DY2022.DBO.#INT_RP

SELECT * 
INTO DY2022.DBO.#INT_RP 
FROM ( 
SELECT 
	B.BILL_NO AS BILLKEY 
	, DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) AS I_DATE 
	, ROUND(( ISNULL(IR.I_RATE,1) /100.0),6) AS I_RATE 
	, ROUND(ADV_TAX_BALANCE * ROUND((ISNULL(IR.I_RATE,1) /100.0),6) ,2) AS I_AMT 
	, CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS I_APPLYDATE 
	, B.BILL_YEAR 
	, B.BILL_NO 
	, 'ADV_TAX' AS I_TYPE 
 	, B.BILL_TYPE 
FROM DY2022.DBO.VBILL_BALANCE_7 V7 
cross join (
			select top 84 realkey, row_number() over( order by realkey) as rowid 
			from DY2022.DBO.d_real 
			) x
INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY 
INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE  
LEFT JOIN DY2022.DBO.INTEREST_RATES IR ON IR.TAXYEAR=YEAR( DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) ) AND IR.PROPTYPE=B.BILL_TYPE 
LEFT JOIN DY2022.DBO.INTEREST_RP L ON L.BILL_NO=B.BILL_NO AND L.BILL_YEAR=B.BILL_YEAR AND L.I_DATE= DATEADD(M,DATEDIFF(M,B.DUE_DATE,GETDATE()) ,B.DUE_DATE+1) 
WHERE V7.ADV_TAX_BALANCE > 0 AND V7.TOTAL_BALANCE>0 
	AND DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) < GETDATE()
	AND B.BILL_TYPE != 'M' 
 			AND B.DUE_DATE < GETDATE() 
 			AND L.I_DATE IS NULL 
			and DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) > isnull((select top 1 paymentdate from dy2022.dbo.vPAYMENT where BILL_NO=b.BILL_NO and BILL_YEAR=b.BILL_YEAR and BILL_TYPE=b.BILL_TYPE order by PAYMENTDATE desc),'')
		--and b.BILL_NO=10467 
		--and b.BILL_YEAR=2022				

UNION ALL 
 
SELECT B.BILL_NO AS BILLKEY 
	, DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) AS I_DATE 
	, ROUND(( ISNULL(IR.I_RATE,1) /100.0),6) AS I_RATE 
	, ROUND(FEES_BALANCE * ROUND((ISNULL(IR.I_RATE,1) /100.0),6) ,2) AS I_AMT 
	, CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS I_APPLYDATE 
	, B.BILL_YEAR 
	, B.BILL_NO 
	, 'FEES' AS I_TYPE 
 	, B.BILL_TYPE 
FROM DY2022.DBO.VBILL_BALANCE_7 V7 
cross join (
			select top 84 realkey, row_number() over( order by realkey) as rowid 
			from DY2022.DBO.d_real 
			) x
INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY 
INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE  
LEFT JOIN DY2022.DBO.INTEREST_RATES IR ON IR.TAXYEAR=YEAR( DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) ) AND IR.PROPTYPE=B.BILL_TYPE 
LEFT JOIN DY2022.DBO.INTEREST_RP L ON L.BILL_NO=B.BILL_NO AND L.BILL_YEAR=B.BILL_YEAR AND L.I_DATE= DATEADD(M,DATEDIFF(M,B.DUE_DATE,GETDATE()) ,B.DUE_DATE+1) 
WHERE V7.FEES_BALANCE > 0 AND V7.TOTAL_BALANCE>0 
	AND DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) < GETDATE()
	AND B.BILL_TYPE != 'M' 
 			AND B.DUE_DATE < GETDATE() 
 			AND L.I_DATE IS NULL 
			and DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) > isnull((select top 1 paymentdate from dy2022.dbo.vPAYMENT where BILL_NO=b.BILL_NO and BILL_YEAR=b.BILL_YEAR and BILL_TYPE=b.BILL_TYPE order by PAYMENTDATE desc),'')
		--and b.BILL_NO=10467 
		--and b.BILL_YEAR<2022

UNION ALL 
 
SELECT B.BILL_NO AS BILLKEY 
	, DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) AS I_DATE 
	, ROUND(( ISNULL(IR.I_RATE,1) /100.0),6) AS I_RATE 
	, ROUND(FEES_OTHER_BALANCE * ROUND((ISNULL(IR.I_RATE,1) /100.0),6) ,2) AS I_AMT 
	, CASE WHEN P.BANKRUPT=1 THEN NULL ELSE GETDATE() END AS I_APPLYDATE 
	, B.BILL_YEAR 
	, B.BILL_NO 
	, 'FEES_OTHER' AS I_TYPE 
 	, B.BILL_TYPE 
FROM DY2022.DBO.VBILL_BALANCE_7 V7 
cross join (
			select top 84 realkey, row_number() over( order by realkey) as rowid 
			from DY2022.DBO.d_real 
			) x
INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=V7.BILL_NO AND B.BILL_YEAR=V7.BILL_YEAR AND B.BALANCE_DUE>0 AND B.BILLKEY=V7.BILLKEY 
INNER JOIN DY2022.DBO.VPROPERTY P ON P.PROPERTYKEY=B.PROPERTYKEY AND P.DIGYR=B.BILL_YEAR AND B.BILL_TYPE=P.PROPTYPE  
LEFT JOIN DY2022.DBO.INTEREST_RATES IR ON IR.TAXYEAR=YEAR( DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) ) AND IR.PROPTYPE=B.BILL_TYPE 
LEFT JOIN DY2022.DBO.INTEREST_RP L ON L.BILL_NO=B.BILL_NO AND L.BILL_YEAR=B.BILL_YEAR AND L.I_DATE= DATEADD(M,DATEDIFF(M,B.DUE_DATE,GETDATE()) ,B.DUE_DATE+1) 
WHERE V7.FEES_OTHER_BALANCE > 0 AND V7.TOTAL_BALANCE>0 
	AND DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) < GETDATE()
	AND B.BILL_TYPE != 'M' 
 			AND B.DUE_DATE < GETDATE() 
 			AND L.I_DATE IS NULL 
			and DATEADD(M,x.rowid-1 ,B.DUE_DATE+1) > isnull((select top 1 paymentdate from dy2022.dbo.vPAYMENT where BILL_NO=b.BILL_NO and BILL_YEAR=b.BILL_YEAR and BILL_TYPE=b.BILL_TYPE order by PAYMENTDATE desc),'')
		--and b.BILL_NO=10467 
		--and b.BILL_YEAR<2022
) X 


INSERT INTO DY2022.DBO.INTEREST_RP ( 
	BILLKEY 
	, I_DATE 
	, I_RATE 
	, I_AMT 
	, I_APPLYDATE 
	, BILL_YEAR 
	, BILL_NO 
	, I_TYPE 
	) 
SELECT  
	I.BILLKEY 
	, I.I_DATE 
	, I.I_RATE 
	, I.I_AMT 
	, I.I_APPLYDATE 
	, I.BILL_YEAR 
	, I.BILL_NO 
	, I.I_TYPE 
FROM DY2022.DBO.#INT_RP I
	INNER JOIN DY2022.DBO.VBILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR 

    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2022.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2022  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2021.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2021  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2020.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2020  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2019.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2019  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2018.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2018  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2017.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2017  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2016.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2016  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2015.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2015  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2014.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2014  AND BU.BILL_TYPE NOT IN ('H','M','T') ; 
 
 
 
 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2022.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2022  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2021.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2021  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2020.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2020  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2019.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2019  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2018.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2018  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2017.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2017  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2016.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2016  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2015.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2015  AND BU.BILL_TYPE IN ('H','T') ; 
 
    UPDATE BU SET BU.INTEREST= BU.INTEREST + I.I_AMT 
        , BU.TOTAL = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
        , BU.BALANCE_DUE = ISNULL(BU.ADV_TAX,0) + ISNULL(BU.INTEREST + I.I_AMT,0) + ISNULL(BU.PENALTY,0) + ISNULL(BU.FIFA,0) + 
            ISNULL(BU.GED,0) + ISNULL(BU.BREACH,0) + ISNULL(BU.ADVERTISING,0) + ISNULL(BU.TITLE_SEARCH,0) + 
            ISNULL(BU.MAIL_FEES,0) + ISNULL(BU.ADMINISTRATION,0) + ISNULL(BU.CREDIT_CARD_FEES,0) + ISNULL(BU.EXCESS_FUNDS,0) + 
            ISNULL(BU.FEES,0) + ISNULL(BU.FEES_OTHER,0) + ISNULL(BU.COLLECT_FEES,0) + ISNULL(BU.PRIOR_YEARS,0) 
    FROM DY2014.DBO.BILL BU 
    INNER JOIN (SELECT BILL_NO, BILL_YEAR, SUM(I_AMT) AS I_AMT FROM DY2022.DBO.#INT_RP WHERE I_APPLYDATE IS NOT NULL GROUP BY BILL_NO, BILL_YEAR) I ON I.BILL_NO=BU.BILL_NO AND I.BILL_YEAR=BU.BILL_YEAR AND BU.BILL_YEAR=2014  AND BU.BILL_TYPE IN ('H','T') ; 
 
 
 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2022.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2021.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2020.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2019.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2018.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2017.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2016.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2015.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
UPDATE BU SET BU.BALANCE_DUE = V7.TOTAL_BALANCE FROM DY2014.DBO.BILL BU INNER JOIN DY2022.DBO.#INT_RP P ON P.BILL_NO=BU.BILL_NO AND P.BILL_YEAR=BU.BILL_YEAR INNER JOIN DY2022.DBO.VBILL_BALANCE_7 V7 ON V7.BILL_NO=BU.BILL_NO AND V7.BILL_YEAR	= BU.BILL_YEAR AND BU.BALANCE_DUE <> V7.TOTAL_BALANCE AND BU.BALANCE_DUE>0 AND BU.BILL_TYPE !='M' 
 
INSERT INTO DY2022.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2022.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2021.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2021.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2020.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2020.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2019.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2019.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2018.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2018.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2017.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2017.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2016.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2016.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;
 
INSERT INTO DY2015.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2015.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;

INSERT INTO DY2014.DBO.BILL_TRANSACTIONS (BILL_NO, BILL_YEAR, T_AMOUNT, T_DATE, T_TYPE, COMMENTS, LOGINID, COMPNAME) 
SELECT I.BILLKEY, I.BILL_YEAR, I_AMT, I_DATE, 'INTEREST_RP', 'PROCESSED ON '+CONVERT(VARCHAR(30),I_APPLYDATE), 'SYSGEN', HOST_NAME() 
FROM DY2022.DBO.#INT_RP I 
INNER JOIN DY2014.DBO.BILL B ON B.BILL_NO=I.BILL_NO AND B.BILL_YEAR=I.BILL_YEAR AND I.BILL_TYPE <> 'M' ;