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

USE [PRECALCDB]
GO

DECLARE @Dim_Time_Start AS INT = 20090101;
DECLARE @Dim_Time_End AS INT = 20111231;
DECLARE @NumberOfFactRows AS INT = 1000;


SET NOCOUNT ON 

DECLARE @Dim_Time_Start_Date AS SMALLDATETIME = CONVERT(SMALLDATETIME, CONVERT(NVARCHAR, @Dim_Time_Start), 112);

-- Clear fact tables to make sure we have no reference integrity problems
TRUNCATE TABLE Fact_GL;
TRUNCATE TABLE Fact_GL_CB;

-- Populate time dimension
DELETE FROM [Dim_Time];

WITH Time_Primer AS
(
	SELECT
		@Dim_Time_Start_Date AS [Date],
		@Dim_Time_Start AS [Day_Key]
	UNION ALL
	SELECT
		DATEADD(DAY, 1, [Date]) AS [Date],
		CONVERT(INT, CONVERT(VARCHAR, DATEADD(DAY, 1, [Date]), 112)) AS [Day_Key]
	FROM Time_Primer
	WHERE [Day_Key] < @Dim_Time_End
)
INSERT INTO Dim_Time([Date], [Year_Key], [Month_Key], [Day_Key])
SELECT 
	[Date],
	[Day_Key] / 10000 AS [Year_Key],
	[Day_Key] / 100 AS [Month_Key],
	[Day_Key]
FROM Time_Primer
OPTION (MAXRECURSION 0);

-- Populate company dimension
DELETE FROM Dim_Company;
INSERT INTO Dim_Company(Company_Key, Company_Name)
VALUES (N'A', N'AUDI');
INSERT INTO Dim_Company(Company_Key, Company_Name)
VALUES (N'B', N'BENTLEY');

-- Populate account dimension
DELETE FROM Dim_Account;
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'1910', N'Cash', N'A', N'Assets');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'1510', N'Accounts Receivable', N'A', N'Assets');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'2440', N'Accounts Payable', N'L', N'Liabilities');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'2390', N'Long term Debt', N'L', N'Liabilities');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'2081', N'Common Stock', N'S', N'Stockholder''s Equity');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'3010', N'Sales Revenue', N'R', N'Revenue');
INSERT INTO Dim_Account([Account_Key], [Account_Name], [Group_Key], [Group_Name])
VALUES (N'4010', N'Cost of Materials', N'C', N'Costs');

-- Insert initial transactions
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (1000000, @Dim_Time_Start_Date, @Dim_Time_Start, N'A', N'1910')
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (1000000, @Dim_Time_Start_Date, @Dim_Time_Start, N'B', N'1910')
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (-100000, @Dim_Time_Start_Date, @Dim_Time_Start, N'A', N'2081')
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (-100000, @Dim_Time_Start_Date, @Dim_Time_Start, N'B', N'2081')
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (-900000, @Dim_Time_Start_Date, @Dim_Time_Start, N'A', N'2390')
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
VALUES (-900000, @Dim_Time_Start_Date, @Dim_Time_Start, N'B', N'2390')

-- Create table of random numbers, used to generate transactions
CREATE TABLE #RandSeq
(
	[Amount]	NUMERIC(18,2),
	[Date]		SMALLDATETIME,
	[Company_Key]	NVARCHAR(10)
)

DECLARE @Counter AS INT = 1
DECLARE @Amount AS NUMERIC(18,2)
DECLARE @Date AS SMALLDATETIME
DECLARE @Company_Key AS NVARCHAR(10)
DECLARE @Total_Days AS INT = DATEDIFF(DAY, CONVERT(SMALLDATETIME, CONVERT(VARCHAR, @Dim_Time_Start), 112), CONVERT(SMALLDATETIME, CONVERT(VARCHAR, @Dim_Time_End), 112))
WHILE @Counter <= @NumberOfFactRows
	BEGIN
		SET @Amount = RAND() * 100000;
		SET @Date = DATEADD(DAY, FLOOR(RAND() * (@Total_Days - 40)), CONVERT(SMALLDATETIME, CONVERT(VARCHAR, @Dim_Time_Start), 112))
		SET @Company_Key = CASE WHEN RAND() <= 0.5 THEN N'A' ELSE N'B' END
		INSERT INTO #RandSeq([Amount], [Date], [Company_Key])
		VALUES (@Amount, @Date, @Company_Key)

		SET @Counter = @Counter + 1
	END;

-- Insert transactions into fact table
-- Make them as sales, accounts receivables, cash, 
-- purchasing (at 90% of sales price) and accounts payables
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	-Amount, 
	[Date],
	CONVERT(INT, CONVERT(VARCHAR, [Date], 112)),
	Company_Key,
	N'3010'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	Amount, 
	[Date],
	CONVERT(INT, CONVERT(VARCHAR, [Date], 112)),
	Company_Key,
	N'1510'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	-Amount, 
	DATEADD(DAY, 40, [Date]),
	CONVERT(INT, CONVERT(VARCHAR, DATEADD(DAY, 40, [Date]), 112)),
	Company_Key,
	N'1510'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	Amount, 
	DATEADD(DAY, 40, [Date]),
	CONVERT(INT, CONVERT(VARCHAR, DATEADD(DAY, 40, [Date]), 112)),
	Company_Key,
	N'1910'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	-Amount * 0.90, 
	[Date],
	CONVERT(INT, CONVERT(VARCHAR, [Date], 112)),
	Company_Key,
	N'2440'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	Amount * 0.90, 
	[Date],
	CONVERT(INT, CONVERT(VARCHAR, [Date], 112)),
	Company_Key,
	N'4010'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	-Amount * 0.90, 
	DATEADD(DAY, 35, [Date]),
	CONVERT(INT, CONVERT(VARCHAR, DATEADD(DAY, 35, [Date]), 112)),
	Company_Key,
	N'1910'
FROM #RandSeq;
INSERT INTO Fact_GL(Amount, [Date], Day_Key, Company_Key, Account_Key)
SELECT 
	Amount * 0.90, 
	DATEADD(DAY, 35, [Date]),
	CONVERT(INT, CONVERT(VARCHAR, DATEADD(DAY, 35, [Date]), 112)),
	Company_Key,
	N'2440'
FROM #RandSeq;

-- Clean up
DROP TABLE #RandSeq;