-- Precalculated closing balances example
-- (C) Johan Åhlén, 2009

USE [PRECALCDB]
GO


SET NOCOUNT ON 

-- Clear the FACT_GL_CB table
TRUNCATE TABLE Fact_GL_CB;

-- Create range table
SELECT DISTINCT
	Company_Key,
	Account_Key,
	(SELECT MIN([Date]) FROM Fact_GL) AS [Date],
	(SELECT MAX([Date]) FROM Fact_GL) AS [MaxDate]
INTO #Range
FROM Fact_GL
WHERE Account_Key < N'3';

-- Create a daily sums table
WITH
AllDays AS
(
	SELECT 
		Company_Key, 
		Account_Key, 
		[Date], 
		[MaxDate]
	FROM #Range

	UNION ALL

	SELECT
		Company_Key, 
		Account_Key, 
		DATEADD(DAY, 1, [Date]), 
		[MaxDate]
	FROM AllDays
	WHERE [Date] < [MaxDate]
)
SELECT
	AllDays.Company_Key, 
	AllDays.Account_Key, 
	AllDays.[Date], 
	ISNULL(SUM(Amount), 0) [Amount]
INTO #DailySums
FROM AllDays
LEFT JOIN Fact_GL ON AllDays.Company_Key = Fact_GL.Company_Key AND AllDays.Account_Key = Fact_GL.Account_Key AND AllDays.[Date] = Fact_GL.[Date] 
GROUP BY AllDays.Company_Key, AllDays.Account_Key, AllDays.[Date]
OPTION (MAXRECURSION 0);

-- Create an index to speed up the next step
CREATE UNIQUE NONCLUSTERED INDEX IX_DailySums
ON #DailySums(Company_Key, Account_Key, [Date]);

-- Aggregate the amount and insert into Fact_GL_CB
WITH
Accumulator AS
(
	SELECT 
		Company_Key, 
		Account_Key, 
		[Date], 
		[Amount]
	FROM #DailySums
	WHERE [Date] = (SELECT MIN([Date]) FROM #Range)

	UNION ALL

	SELECT
		#DailySums.Company_Key, 
		#DailySums.Account_Key, 
		#DailySums.[Date], 
		#DailySums.[Amount] + Accumulator.[Amount]
	FROM #DailySums
	JOIN Accumulator
	ON
		Accumulator.Company_Key = #DailySums.Company_Key AND
		Accumulator.Account_Key = #DailySums.Account_Key AND
		DATEADD(DAY, 1, Accumulator.[Date]) = #DailySums.[Date]
)
INSERT INTO Fact_GL_CB(Amount_CB, [Date], Day_Key, Company_Key, Account_Key)
SELECT
	[Amount],
	[Date],
	CONVERT(INT, CONVERT(NVARCHAR, [Date], 112)) [Day_Key],
	Company_Key,
	Account_Key
FROM Accumulator
ORDER BY Company_Key, Account_Key, [Date]
OPTION (MAXRECURSION 0);

-- Clean up
DROP TABLE #DailySums
DROP TABLE #Range