InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 15:36:21 GMT20http://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxThat sounds good. What I will do is work on the aggregation queries and then post my solution using a sample dataset. Then the community can analyze the solution and either use the methodology to help them, or offer a more optimized method.Mon, 07 Jan 2013 11:23:49 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspx[quote][b]steve.pantazis (1/7/2013)[/b][hr]Yes, agreed. I have a steep learning curve ahead of me, but it's necessary. Thanks again for your contribution.[/quote]Stevemay be worth your while posting some "base data examples" here on the forum and see what the collective mindset comes back with......you never know :-)Mon, 07 Jan 2013 11:16:32 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxYes, agreed. I have a steep learning curve ahead of me, but it's necessary. Thanks again for your contribution.Mon, 07 Jan 2013 11:09:46 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspx[quote][b]steve.pantazis (1/7/2013)[/b][hr]........ Long story short, we need to rewrite our aggregation routines, and it looks like you're pointing us in the right direction.[/quote]SteveI would entirely agree with your comment and suggest you read and reread the article in question.I am sure that you will be able to increase performance.regardsps....whilst the solution I provided may work...I wouldnt be looking to implement in production :-)...was only suggested to get the discussion going some more...both to help you and to assist my own learning.Mon, 07 Jan 2013 11:04:10 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxJLS, I tried out your code and it works like a champ! Thank you for taking the time to construct the code. I am very interested in testing it out against a larger dataset.Jeff, I appreciate the reference to the article on hierarchy-based aggregations. We have a lot of code that does WTD, MTD, QTD and YTD aggregations against employee and organizational hierarchies that then gets rolled up against a goals hierarchy. The combination results in millions of "summary" records in the course of a year for a large organization. The real pain point is when it comes to reprocessing sales numbers from the beginning of a quarter or year after a customer fixes a problem in their source data or makes a change to which products a goal is tied to. In fact, my search for a more efficient way of aggregating data was precipitated by a credit union that wants to rerun their entire year's numbers. Each day of processing takes 3 1/2 minutes. You can imagine multiplying that by 365. We calculate each sale for the day, then aggregate the totals using cursors up the organizational hierarchy, followed aggregating up the goals hierarchy. After that, we add to the WTD, MTD, QTD and YTD numbers by using a bunch of sums in a series of cursors, done via a separate stored procedure. For a credit union with 5 branches and 40 employees, the processing takes 30 seconds a day. For one with 30 branches and 400 employees, it takes 10 times that amount, which causes a scalability problem in our multi-customer database. Long story short, we need to rewrite our aggregation routines, and it looks like you're pointing us in the right direction.Mon, 07 Jan 2013 09:52:42 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspx[quote][b]steve.pantazis (1/5/2013)[/b][hr]I will definitely try out your code...thanks.The reason for insert into ResultHistory is that we process large quantities of sales data from a detail table containing each account opening that we need to aggregate into totals each night so bankers can view the numbers via our Web app when they walk in the office the next day. [b]We have the equivalent of a data mart that is used for tracking historical sales over time.[/b] Rather than keep the aggregated data in cubes, we prefer relational tables, which makes querying the data via stored procedures and SSRS a breeze.[/quote]Although the method that JLS posted will certainly work, you might want to consider the next logical extension of such a method to help you pre-aggregate more information in more ways than you can shake a stick at in a very short period of time. Please see the following article for an example of what I mean...[url]http://www.sqlservercentral.com/articles/T-SQL/94570/[/url]Sun, 06 Jan 2013 23:47:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxI will definitely try out your code...thanks.The reason for insert into ResultHistory is that we process large quantities of sales data from a detail table containing each account opening that we need to aggregate into totals each night so bankers can view the numbers via our Web app when they walk in the office the next day. We have the equivalent of a data mart that is used for tracking historical sales over time. Rather than keep the aggregated data in cubes, we prefer relational tables, which makes querying the data via stored procedures and SSRS a breeze.Sat, 05 Jan 2013 20:02:46 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspx[quote][b]steve.pantazis (1/4/2013)[/b][hr]I need to find a way to do an INSERT into the same table (named ResultsHistory) by summing up the child row totals without using a CURSOR in MSSQL. We currently use a CURSOR in a stored procedure to aggregate the child rows’ sales totals into parent totals, but this takes a long time with our larger customers. The table stores aggregated historical data that our organization uses to display results quickly through our Web app. We made the business decision a while back to store the aggregated data in a relational table rather than an analysis cube. As a result, the table contains millions of records. [/code][/quote]out of curiosity....how many rows are there in base transaction tables and why do you find it necessary to use a cursor to aggregate these into ResultsHistorySat, 05 Jan 2013 09:45:29 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxpersonally I would not try to insert records into your table.see if this gets anywhere close to what you require....could well be improved I expect.[code="sql"]declare @TP as INTset @TP = 9999;WITH cteOrg AS ( SELECT C.OrgNum AS Bank , B.OrgNum AS Region , A.OrgNum AS Branch FROM dbo.OrgHierarchy AS A INNER JOIN dbo.OrgHierarchy AS B ON A.ParentOrgNum = B.OrgNum INNER JOIN dbo.OrgHierarchy AS C ON B.ParentOrgNum = C.OrgNum ) , cte_allGoals AS ( SELECT dbo.GoalDefs.INum , dbo.GoalDefs.GoalNum , cteOrg.Branch , @TP AS TP FROM dbo.GoalDefs CROSS JOIN cteOrg ) , cte_Results AS ( SELECT AG.INum , AG.GoalNum , AG.Branch , AG.TP , ISNULL( SUM( RH.ResultItems ) , 0 )AS ResultItems , ISNULL( SUM( RH.ResultValue ) , 0 )AS ResultValue FROM cte_AllGoals AS AG LEFT OUTER JOIN dbo.ResultsHistory AS RH ON AG.TP = RH.TimePeriod AND AG.Branch = RH.OrgNum AND AG.GoalNum = RH.GoalNum AND AG.INum = RH.INum GROUP BY AG.INum , AG.GoalNum , AG.Branch , AG.TP ) SELECT cte_Results.INum , cte_Results.GoalNum , cteOrg.Bank AS OrgNum , SUM( cte_Results.ResultItems )AS RI , SUM( cte_Results.ResultValue )AS RV , cte_Results.TP FROM cteOrg INNER JOIN cte_Results ON cteOrg.Branch = cte_Results.Branch GROUP BY cte_Results.INum , cte_Results.GoalNum , cteOrg.Bank , cte_Results.TP UNION ALL SELECT cte_Results.INum , cte_Results.GoalNum , cteOrg.Region AS OrgNum , SUM( cte_Results.ResultItems )AS RI , SUM( cte_Results.ResultValue )AS RV , cte_Results.TP FROM cteOrg INNER JOIN cte_Results ON cteOrg.Branch = cte_Results.Branch GROUP BY cte_Results.INum , cte_Results.GoalNum , cteOrg.Region , cte_Results.TP ORDER BY OrgNum , cte_Results.GoalNum;[/code]Sat, 05 Jan 2013 05:50:24 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxSean, 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.[code="sql"]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 @TimePeriodFROM 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 queryWHERE GoalDefs.INum = @INum[/code]Fri, 04 Jan 2013 16:39:47 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspx[quote][b]steve.pantazis (1/4/2013)[/b][hr]I read over the article on running totals. However, I'm not looking to produce a running total on a set of transactions. I'm trying to sum child rows into parents "buckets" in a single set statement. The dataset I want to produce is shown in the 12-row example. The additional constraint is that I need to produce a parent row with a zero total in the cases where there are no child rows, which is also shown in the 12-row example.[/quote]Well I have to say that I don't understand your requirements here at all. Maybe it is a case of late Friday afternoon but I am missing something. I don't understand at all how you get to your results. I am quite unclear on what you are looking for. An insert into ResultsHistory? What is that insert?Fri, 04 Jan 2013 15:26:03 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxI read over the article on running totals. However, I'm not looking to produce a running total on a set of transactions. I'm trying to sum child rows into parents "buckets" in a single set statement. The dataset I want to produce is shown in the 12-row example. The additional constraint is that I need to produce a parent row with a zero total in the cases where there are no child rows, which is also shown in the 12-row example.Fri, 04 Jan 2013 15:03:07 GMTsteve.pantazishttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxFrom your description it sounds like you want to do running totals? Take a look at this article that explains how to do this quite easily.[url=http://www.sqlservercentral.com/articles/T-SQL/68467/]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]Fri, 04 Jan 2013 14:04:15 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1403054-391-1.aspxI need to find a way to do an INSERT into the same table (named ResultsHistory) by summing up the child row totals without using a CURSOR in MSSQL. We currently use a CURSOR in a stored procedure to aggregate the child rows’ sales totals into parent totals, but this takes a long time with our larger customers. The table stores aggregated historical data that our organization uses to display results quickly through our Web app. We made the business decision a while back to store the aggregated data in a relational table rather than an analysis cube. As a result, the table contains millions of records. The child rows already exist in the ResultsHistory table, so I’m looking to work exclusively with the parents. I've scoured the Web trying to find the most efficient method of doing the totaling within a single INSERT statement. Performance is the key objective. Some options include using CTE to recursively cycle through the parents, along with the potential of using WITH ROLLUP and CUBE for the aggregation. I'm not experienced with any of these newer SQL Server techniques for aggregating relational data, so I'm stuck.Below is some sample data for our ResultsHistory table, along with a couple of lookup tables and a table containing the organization’s hierarchy.The ResultsHistory table contains sales totals for banking products, such as CDs, Savings and Checking. The INum field represents which Banking customer we're dealing with, so it is always used in a JOIN to the other tables. The TimePeriod field contains a numerical value that represents a particular day (e.g. 9999 = 1/3/13). The GoalNum field relates to the goal product in the GoalDefs table, and the OrgNum says which organizational unit the record belongs to, which cross-references to the GroupDefs table and its hierarchical extension table, OrgHierarchy. The ResultItems field contains the total number of sales and the ResultsValue field contains the total dollars in sales.I need to insert new records into ResultsHistory for the day's sales totals for the parent groups. If you look at GroupDefs, you'll see that there is a RollUpFlg value of either 'Y' or 'N'. All the existing child records in ResultsHistory have corresponding records in GroupDefs with a RollUpFlg value of 'N'. The parents have a RollUpFlg value of 'Y'.Basically, I need to add one record for each GoalNum and OrgNum for the parent groups, regardless of whether the children had any sales, which means that I will have to insert rows for parents with zeros for ResultItems and ResultValue if there are no sales for the children. Since we have four goals and three parent groups in our sample data, we need to end up with 12 rows containing the sales totals for one day. The Bank, of course, will have the totals of all the branches, and the regions will have the totals for just their respective branches.Here’s what the data will look like for the Bank and regions after we do our INSERT into ResultsHistory:[img]http://www.stevepantazis.com/images/misc/a_Table%20output.jpg[/img]Back to my question: what’s the most efficient way to do a SINGLE INSERT statement? If you can provide the SQL you think would be the most optimized in terms of performance, I would appreciate it. Like I said, I think we can accomplish this potentially with CTE, CUBE, or WITH ROLLUP, but I don’t know how.[code="sql"] CREATE TABLE ResultsHistory ( INum INT, GoalNum INT, OrgNum INT, ResultItems INT, ResultValue MONEY, TimePeriod INT) CREATE TABLE GroupDefs ( INum INT, OrgNum INT, Description VARCHAR(100), RollUpFlg CHAR(1)) CREATE TABLE GoalDefs ( INum INT, GoalNum INT, Description VARCHAR(100)) CREATE TABLE OrgHierarchy ( INum INT, OrgNum INT, ParentOrgNum INT, OrgLevel INT) INSERT INTO GroupDefs VALUES (7,135,'Bank','Y') INSERT INTO GroupDefs VALUES (7,136,'Region 1','Y') INSERT INTO GroupDefs VALUES (7,137,'Region 2','Y') INSERT INTO GroupDefs VALUES (7,138,'Branch A','N') INSERT INTO GroupDefs VALUES (7,139,'Branch B','N') INSERT INTO GroupDefs VALUES (7,140,'Branch C','N') INSERT INTO OrgHierarchy VALUES (7,135,NULL,1) INSERT INTO OrgHierarchy VALUES (7,136,135,2) INSERT INTO OrgHierarchy VALUES (7,137,135,2) INSERT INTO OrgHierarchy VALUES (7,138,136,3) INSERT INTO OrgHierarchy VALUES (7,139,136,3) INSERT INTO OrgHierarchy VALUES (7,140,137,3) INSERT INTO GoalDefs VALUES (7,1000,'Checking') INSERT INTO GoalDefs VALUES (7,1001,'Savings') INSERT INTO GoalDefs VALUES (7,1002,'CDs') INSERT INTO GoalDefs VALUES (7,1003,'Auto Loans') INSERT INTO ResultsHistory VALUES (7,1000,138,15,100000,9999) INSERT INTO ResultsHistory VALUES (7,1000,139,20,150000,9999) INSERT INTO ResultsHistory VALUES (7,1000,140,10,100000,9999) INSERT INTO ResultsHistory VALUES (7,1001,138,25,80000,9999) INSERT INTO ResultsHistory VALUES (7,1001,139,30,110000,9999) INSERT INTO ResultsHistory VALUES (7,1002,138,5,600000,9999) INSERT INTO ResultsHistory VALUES (7,1002,140,7,290000,9999)[/code]Fri, 04 Jan 2013 12:10:48 GMTsteve.pantazis