SQLTeam.com | Weblogs | Forums

Rolling forward total almost working

sql2008

#1

Here's what I have so far from the work "Adam Jacobson". did for me on another site.

This is almost working.

The data is selected on a Date Range 08/01/16 - 12/31/16. I convert the 2 dates to get the start and ending periods. Then base on the number of periods will be the column heading except the Previous Previous period. I am only getting the starting Beginning balance from that period. After that the sum of each period will be the beginning balance the next period. This will happen until all period are displayed.

The logic is almost working. The Bal type is not calculating the running balance correctly after Period 9. The $4.00 is coming from Oct that does not display.

The calculating should work like this

Beginning Balance from Oct $4.00

Sep 4 + (11 + 12 + 13) - 12 = 33

Oct 33 + (20 + 21 + 23) -22 = 75

Nov 75 + (31 + 32 + 33) -32 = 139

Dec 139 + (41 + 42 + 43) -42 = 223

Here how the data should look:

B   BAL    2016 9   4.00
T   AP     2016 9   11.00
T   APAJ   2016 9   12.00
T   CJ     2016 9   13.00
T   GEN    2016 9   -12.00
B   BAL    2016 10  24.00     **Should be 33**
T   AP     2016 10  20.00
T   APAJ   2016 10  21.00
T   CJ     2016 10  23.00
T   GEN    2016 10  -22.00
B   BAL    2016 11  42.00     **Should be 75**
T   AP     2016 11  31.00
T   APAJ   2016 11  32.00
T   CJ     2016 11  33.00
T   GEN    2016 11  -32.00
B   BAL    2016 12  64.00     **Should be 139**
T   AP     2016 12  41.00
T   APAJ   2016 12  42.00
T   CJ     2016 12  43.00
T   GEN    2016 12  -42.00

Here's the query

DECLARE @ClosingBalTemp TABLE
(
Account_ID varchar(30),
Period_Nbr varchar(10),
Source_Code varchar(10),
Closing_Balance_Amt numeric(16,2)
)

INSERT INTO @ClosingBalTemp (Account_ID, Period_Nbr, Source_Code, Closing_Balance_Amt)

VALUES ('01-002-333', '2016-008', 'AP', 1),
       ('01-002-333', '2016-008', 'APAJ', 2),
       ('01-002-333', '2016-008', 'CJ', 3),
       ('01-002-333', '2016-008', 'GEN', -2),
       ('01-002-333', '2016-009', 'AP', 11),
       ('01-002-333', '2016-009', 'APAJ', 12),
       ('01-002-333', '2016-009', 'CJ', 13),
       ('01-002-333', '2016-009', 'GEN', -12),
       ('01-002-333', '2016-010', 'AP', 20),
       ('01-002-333', '2016-010', 'APAJ', 21),
       ('01-002-333', '2016-010', 'CJ', 23),
       ('01-002-333', '2016-010', 'GEN', -22),
       ('01-002-333', '2016-011', 'AP', 31),
       ('01-002-333', '2016-011', 'APAJ', 32),
       ('01-002-333', '2016-011', 'CJ', 33),
       ('01-002-333', '2016-011', 'GEN', -32),
       ('01-002-333', '2016-012', 'AP', 41),
       ('01-002-333', '2016-012', 'APAJ', 42),
       ('01-002-333', '2016-012', 'CJ', 43),
       ('01-002-333', '2016-012', 'GEN', -42)

--SELECT * FROM @ClosingBalTemp

DECLARE @CurrentPeriod CHAR(10);
DECLARE @PriorPeriod CHAR(10);
DECLARE @PPPeriod CHAR(10)

SET @CurrentPeriod = '2016-012';
SET @PriorPeriod = '2016-009';
SET @PPPeriod = '2016-008';
-- First Step - Summarize the balances
WITH Balances (CurrentPeriodOpenBal, PriorPeriodOpenBal)
  AS (
     SELECT SUM(CASE WHEN cbt.Period_Nbr IN (@PPPeriod, @PriorPeriod)
            THEN cbt.Closing_Balance_Amt ELSE 0 END)
            AS CurrentPeriodOpenBal,
        SUM(CASE WHEN cbt.Period_Nbr IN (@PPPeriod) 
            THEN cbt.Closing_Balance_Amt ELSE 0 END) 
            AS PriorPeriodOpenBal
        FROM @ClosingBalTemp cbt
        WHERE cbt.Period_Nbr < @CurrentPeriod
  )
-- Now we are going to combine our balances and our transaction
,
BalTrans (TransType, SourceCode, PeriodNbr, Amount) AS
(
SELECT 'B', 'BAL', @PriorPeriod, bal.PriorPeriodOpenBal 
FROM Balances bal   
UNION ALL 
SELECT 'B', 'BAL', @CurrentPeriod, bal.CurrentPeriodOpenBal 
FROM Balances  bal
UNION ALL  
SELECT 'T', trans.Source_Code, trans.Period_Nbr,  trans.Closing_Balance_Amt
   FROM @ClosingBalTemp trans
  WHERE trans.Period_Nbr BETWEEN @PriorPeriod AND @CurrentPeriod
)
-- Now we simply order our transactions   
SELECT bt.TransType, bt.SourceCode, bt.PeriodNbr, bt.Amount FROM BalTrans bt
 ORDER BY bt.PeriodNbr, bt.TransType, bt.SourceCode