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