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)')