MYOB Exo Business – Faster Creditors Recalc script

The logic of the last two posts extends to Creditor Recalculations, which can be done fast using the attached script.

IF NOT EXISTS (select 1 from sys.procedures where name='X_RECALC_CREDITORS')
EXEC ('CREATE PROCEDURE X_RECALC_CREDITORS AS
--AUTHOR:	Will Howard, 2016/12/22 (I just wrote a wrapper around Alex''s awesome work)
--Purpose:	Allow easy call to do a fast recalc of debtors.
;WITH
CRT AS''
(   SELECT T.ACCNO
       ,ROUND(SUM(CASE WHEN P.AGE=0 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB0
       ,ROUND(SUM(CASE WHEN P.AGE=1 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB1
       ,ROUND(SUM(CASE WHEN P.AGE=2 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB2
       ,ROUND(SUM(CASE WHEN P.AGE>2 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB3
       ,ROUND(SUM(CASE WHEN P.AGE=1 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB0
       ,ROUND(SUM(CASE WHEN P.AGE=2 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB1
       ,ROUND(SUM(CASE WHEN P.AGE=3 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB2
       ,ROUND(SUM(CASE WHEN P.AGE>3 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB3
	FROM   CR_TRANS T INNER JOIN
	PERIOD_STATUS AS P ON P.SEQNO=T.PERIOD_SEQNO
	GROUP BY T.ACCNO)
SELECT A.ACCNO
    ,A.NAME
    ,A.AGEDBAL0, AB0 AS CALC_AGE0
    ,A.AGEDBAL1, AB1 AS CALC_AGE1
    ,A.AGEDBAL2, AB2 AS CALC_AGE2
    ,A.AGEDBAL3, AB3 AS CALC_AGE3
    ,A.PRIOR_AGEDBAL0, PB0 AS CALC_PRIOR0
    ,A.PRIOR_AGEDBAL1, PB1 AS CALC_PRIOR1
    ,A.PRIOR_AGEDBAL2, PB2 AS CALC_PRIOR2
    ,A.PRIOR_AGEDBAL3, PB3 AS CALC_PRIOR3
FROM   CR_ACCS AS A LEFT OUTER JOIN
        CRT AS T ON T.ACCNO=A.ACCNO
WHERE  A.AGEDBAL0 <> ISNULL(AB0,0) OR
		A.AGEDBAL1 <> ISNULL(AB1,0) OR
		A.AGEDBAL2 <> ISNULL(AB2,0) OR
		A.AGEDBAL3 <> ISNULL(AB3,0) OR
		A.PRIOR_AGEDBAL0 <> ISNULL(PB0,0) OR
		A.PRIOR_AGEDBAL1 <> ISNULL(PB1,0) OR
		A.PRIOR_AGEDBAL2 <> ISNULL(PB2,0) OR
		A.PRIOR_AGEDBAL3 <> ISNULL(PB3,0)
;WITH
CRT AS
(   SELECT T.ACCNO
       ,ROUND(SUM(CASE WHEN P.AGE=0 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB0
       ,ROUND(SUM(CASE WHEN P.AGE=1 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB1
       ,ROUND(SUM(CASE WHEN P.AGE=2 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB2
       ,ROUND(SUM(CASE WHEN P.AGE>2 THEN T.AMOUNT-T.ALLOCATEDBAL ELSE 0 END),2) AS AB3
       ,ROUND(SUM(CASE WHEN P.AGE=1 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB0
       ,ROUND(SUM(CASE WHEN P.AGE=2 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB1
       ,ROUND(SUM(CASE WHEN P.AGE=3 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB2
       ,ROUND(SUM(CASE WHEN P.AGE>3 THEN T.PREV_PERIOD_CLOSE ELSE 0 END),2) AS PB3
	FROM   CR_TRANS T INNER JOIN
	PERIOD_STATUS AS P ON P.SEQNO=T.PERIOD_SEQNO
	GROUP BY T.ACCNO)
UPDATE A
SET  AGEDBAL0=ISNULL(AB0,0)
    ,AGEDBAL1=ISNULL(AB1,0)
    ,AGEDBAL2=ISNULL(AB2,0)
    ,AGEDBAL3=ISNULL(AB3,0)
    ,PRIOR_AGEDBAL0=ISNULL(PB0,0)
    ,PRIOR_AGEDBAL1=ISNULL(PB1,0)
    ,PRIOR_AGEDBAL2=ISNULL(PB2,0)
    ,PRIOR_AGEDBAL3=ISNULL(PB3,0)
FROM   CR_ACCS AS A LEFT OUTER JOIN
        CRT AS T ON T.ACCNO=A.ACCNO
WHERE  A.AGEDBAL0 <> ISNULL(AB0,0) OR
		A.AGEDBAL1 <> ISNULL(AB1,0) OR
		A.AGEDBAL2 <> ISNULL(AB2,0) OR
		A.AGEDBAL3 <> ISNULL(AB3,0) OR
		A.PRIOR_AGEDBAL0 <> ISNULL(PB0,0) OR
		A.PRIOR_AGEDBAL1 <> ISNULL(PB1,0) OR
		A.PRIOR_AGEDBAL2 <> ISNULL(PB2,0) OR
		A.PRIOR_AGEDBAL3 <> ISNULL(PB3,0)')