Hierarchy based aggregation
- by Ganapathy Subramaniam
I have a hierarchy table in SQL Server 2005 which contains employees - managers - department - location - state.
Sample table for hierarchy table:
ID Name ParentID Type
1 PA NULL 0 (group)
2 Pittsburgh 1 1 (subgroup)
3 Accounts 2 1
4 Alex 3 2 (employee)
5 Robin 3 2
6 HR 2 1
7 Robert 6 2
Second one is fact table which contains employee salary details ID and Salary.
Sample data for fact table:
ID Salary
4 6000
5 5000
7 4000
Is there any good to way to display the hierarchy from hierarchy table with aggregated sum of salary based on employees. Expected result is like
Name Salary
PA 15000 (Pittsburgh + others(if any))
Pittusburgh 15000 (Accounts + HR)
Accounts 11000 (Alex + Robin)
Alex 6000 (direct values)
Robin 5000
HR 4000
Robert 4000
In my production environment, hierarchy table may contain 23000+ rows and fact table may contain 300,000+ rows. So, I thought of providing any level of groupid to the query to retrieve just its children and its corresponding aggregated value. Any better solution?
