Hi there,
I try to calculate the account balances of the AdventureWorksDW table FactFinance. The corresponding DimAccount table is recursive.
AccountKey ParentAccountKey AccountDescriptionEN
47 NULL Net Income
48 47 Operating Profit
49 48 Gross Margin
50 49 Net Sales
51 50 Gross Sales
52 51 Intercompany Sales
53 50 Returns and Adjustments
54 50 Discounts
...
Each row in FactFinance refers to the leaf in the account hierarchy. Structure is: AccountKey | Amount
Following cte provides the hierarchy:
WITH cte (AccountKey, ParentAccountKey)
AS (
SELECT AccountKey, ParentAccountKey
FROM DimAccount
WHERE DimAccount.AccountKey = 47
UNION ALL
SELECT r.AccountKey, r.ParentAccountKey
FRO ...
Go to the complete details ...