0

We have some tables in our database that pretty much grow infinitely so we are trying to create a task that will rollup old data on a periodic basis (probably daily) and remove the records that are contained in the rollup.

This is reasonably easy to do just using a GROUP BY. The catch is that are child tables that need to be rolled up as well and refer to the new completed parent table rows that they refer to and I have no idea how to do that without doing some really lame subqueries.

As an example say I start with the following data in the two tables:

Parent Table (ID is an IDENTITY column)

ID  Type  Amount
--  ----  ------
 1     1   10.00
 2     1    3.00
 3     3    8.00
 4     3    9.00

Child Table

ParentID  Thing  Qty
--------  -----  ---
       1      8    3
       1      6    6
       2      8    4
       2      5    3
       3      8    2
       3      5    1
       4      5    4

First I would roll up the parent table into new rows that are grouped by Type and then the Amounts are summed together which would give the following:

ID  Type  Amount
--  ----  ------
 5     1   13.00
 6     3   17.00

The query to create the above data in Parent the query would look something like the following:

INSERT INTO Parent (Type, Amount)
SELECT Type, SUM(Amount) AS Amount
FROM Parent
GROUP BY Type

For the child table I want to do something similar, except grouping by "Thing" and summing Quantity with the ParentID referring to the newly created Parent that corresponds to the original Parent. This would create something like the following:

ParentID  Thing  Qty
--------  -----  ---
       5      8    7
       5      5    3
       5      6    6
       6      5    5
       6      8    2

This query would look something similar to the following but I don't know how to get the ParentID or how to link to the Parent table:

INSERT INTO Child (ParentID, Thing, Qty)
SELECT ?, Thing, SUM(Qty) AS Qty
FROM Child
INNER JOIN Parent
    ON ? = ?
GROUP BY ?, Thing

I would assume that this is something that happens somewhat frequently so does anyone know what the correct way to handle this situation is?

Dorky Engineer
  • 1,054
  • 2
  • 7
  • 9
  • Parent.Type and Child.Group are equal ? I mean they are named differentlt, but are they referencing the same content ? – NeedAnswers Jun 25 '14 at 04:10
  • Can u pls check the query given below and getback – Azar Jun 25 '14 at 04:41
  • Parent.Type and Child.Group are not intended to be related in any way. They are just some random data I made up. They could be any value. – Dorky Engineer Jun 25 '14 at 05:09
  • I changed the example data to make the Group column be named "Thing" to avoid confusion with SQL group and also to make it clear that Type and Thing do not correlate to each other. – Dorky Engineer Jun 25 '14 at 07:46

1 Answers1

0

First Table:

Declare @max int = (Select MAX(ID) from parent)
Select @max

Select @max+R ID,type,Amount
from 
(
Select type,sum(amount) 'Amount',ROW_NUMBER() over(order by type) 'R'
from parent
group by Type
) R

Second Table:

Select @max+TYPE 'ParentID',[group],sum(Qty) 'Qty'
from child
join parent 
on id = parentid
group by type,[group]
order by [parentid],[group]
Azar
  • 1,852
  • 15
  • 17