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

Files:

Example Data to import:

CompanyHierarchy.txt
Company.txt
sales.txt

Rate