Sean, I'm looking to do a single INSERT statement, similar to the one below. The gist of it is that the outer query needs to insert all the parent group records into the ResultsHistory with the sum of the child sales totals from the inner (aggregated) query. If the child groups didn't do any sales for the respective goal, we still need to place a zero total at the parent level (hence the user of the LEFT OUTER JOIN).

That said, the join between the inner and outer queries are obviously wrong because I don't know how to do the necessary CTE, CUBE or WITH ROLLUP technique that aggregates the child totals into the parents and links the children to their parents. Remember, the child group data already exists in ResultsHistory. What we're doing is adding in the parent records with the sum of the child groups' sales.

INSERT INTO ResultsHistory (

INum,

GoalNum,

OrgNum,

ResultItems,

ResultValue,

TimePeriod)

SELECT

@INum,

GoalDefs.GoalNum,

GroupDefs.OrgNum,

CASE ItemsTotal WHEN NULL THEN 0 ELSE ItemsTotal END, -- If the value in the aggregated join is missing, then we need to use a zero value

CASE ValueTotal WHEN NULL THEN 0 ELSE ValueTotal END, -- If the value in the aggregated join is missing, then we need to use a zero value

@TimePeriod

FROM

GoalDefs

INNER JOIN GroupDefs ON ( -- Perform limited cross join to get all goals and all parent groups

GoalDefs.INum = GroupDefs.INum AND

GroupDefs.RollUpFlg = 'Y') -- Consider only the parent groups

INNER JOIN OrgHierarchy ON ( -- Join to this table will probably require CTE to determine all the subordinate groups that will be used for totaling

GroupDefs.INum = OrgHierarchy.INum AND

GroupDefs.OrgNum = OrgHierarchy.OrgNum)

LEFT OUTER JOIN ( -- This aggregated join will proabaly need to be replaced by CTE; we're using a left outer join to ensure all goals are accounted for, regardless of whether the child groups have any sales

SELECT

GroupDefs.OrgNum, -- Child group that already exists in

SUM(ResultItems) ItemsTotal,

SUM(ResultValue) ValueTotal

FROM

ResultsHistory

INNER JOIN GroupDefs ON (

ResultsHistory.INum = GroupDefs.INum AND

ResultsHistory.OrgNum = GroupDefs.OrgNum AND

GroupDefs.RollUpFlg = 'N') -- Consider only the child groups

WHERE

ResultsHistory.INum = @INum

GROUP BY

GroupDefs.OrgNum) ChildTotals ON (

ChildTotals.OrgNum = OrgHierarchy.OrgNum) -- *** This is wrong because we need to use CTE or some other method to sum the child groups in the aggregated query and tie them to the parent groups in the outer query

WHERE

GoalDefs.INum = @INum