Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Aggregating a Set of Hierachical Records

By Les Smith,

Aggregating a Set of Hierachical Records

Much of the business data that we work is hierachical. For example, if we are maintaining a customer list we may be dealing with a large corporation that owns seveal companies. A child company could also be a parent to another company.

Each company is a separate legal and billing entity. However we need to develop meaningful reports to determine how much business we are doing with the whole parent organization. In my example we have a parent organization called General Company. Our goal is to summarize sales for all companies that belong to General Company.

We have created a table called CompanyHierarchy which records the relationships between Companies. There are two fields in the table: ParentCompanyID and CompanyID. The ParentCompanyID field holds the parent company's CompanyID. We might also choose not to use a linkage table-in that case we would create ParentCompanyID field in the Companies table and modify the stored procedures.

The two stored procedures, sp_sumCompanySales and sp_getCompanies, are used to create a temporary table that holds the parent company and all its children's CompanyID records. Once we have this temporary table we can use it to aggregate data. In the example we have built up our company list and then use it to aggregate sales figures.

The procedure sp_sumCompanySales invokes sp_getCompanies to get the list and then performs the aggregation. In the procedure sp_getCompanies we are recursively going down from the parent to the lowest children.

Example Usage:

sp_sumCompanySales 1 --summarizes all Companies because Company 1 is the master

sp_sumCompanySales 2 --summarizes all Companies except 1 (the parent) and 3 (a child of 1)

References:

Q248915 - INF: How to Show Expanding Hierarchies Using SQL Server

Files:

Companies.sql
CompanyHierarchy.sql
sp_getCompanies.sql
sp_sumCompanySales.sql

Example Data to import:

CompanyHierarchy.txt
Company.txt
sales.txt


Return to Les Smith's Home

 

Total article views: 3016 | Views in the last 30 days: 0
 
Related Articles
FORUM

using aggregate function on calculated member value in tablix

apply aggregate function in parent rowgroup

FORUM

Aggregation

Confused about aggregation

FORUM

Child and parent Relation

Child and parent Relation

FORUM

Triggers Examples

Triggers Examples

BLOG

Using Unary Operators to control Analysis Services hierarchy aggregations

Analysis Services hierarchy aggregations can be easily controlled using unary operators. For example...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones