How to correctly GROUP this Data Set

Question

I am looking at shipment data for the past 12 months and want the total finished goods units shipped and their raw material counter parts.

I have joined the shipment detail table with the bill of materials header (which has the corresponding finished good item) and then joined the BOM HEader to the BOM Detail to get all the Raw Material components and quantities per Finished Good Unit.

ShipYear    ShipMonth   CLASS   SHIPMENT_ID INTERNAL_SHIPMENT_LINE_NUM  FG_ITEM FG_QTY  RM_ITEM RM_QTY_PER_FG_UNIT  TOTAL_RM_QTY
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010932    2   4
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010933    3   6
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010934    1   2
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010935    4   8
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010936    1   2
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010937    1   2
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010938    1   2
2013    6   SHADE CHIPS 9701316 25851201    PM9000015050    2   PM1000010939    1   2
2013    6   SHADE CHIPS 9701316 25851202    PM9000015074    5   PM1000010932    4   20
2013    6   SHADE CHIPS 9701316 25851202    PM9000015074    5   PM1000010933    1   5
2013    6   SHADE CHIPS 9701316 25851202    PM9000015074    5   PM1000010934    3   15
2013    6   SHADE CHIPS 9701316 25851202    PM9000015074    5   PM1000010935    8   40
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010932    1   1
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010933    1   1
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010934    1   1
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010935    4   4
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010936    1   1
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010937    2   2
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010938    3   3
2013    6   SHADE CHIPS 9701638 25853677    PM9000015394    1   PM1000010939    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010932    7   7
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010933    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010934    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010935    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010936    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010937    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010938    1   1
2013    6   SHADE CHIPS 9701639 25853678    PM9000015404    1   PM1000010939    1   1
TOTALS                      9       58  136

Here is a pic that is formatted a little better:

In the end, I want to see the following:

Year    Month   Class          FG Units RM Units
2012    6   SHADE CHIPS 3449    50351
2012    7   SHADE CHIPS 288 3714
2012    8   SHADE CHIPS 282 4498
2012    9   SHADE CHIPS 105 1528
2012    12  SHADE CHIPS 539 4002
2013    1   SHADE CHIPS 1972    15284
2013    2   SHADE CHIPS 121 781
2013    3   SHADE CHIPS 60  808
2013    4   SHADE CHIPS 74  1335
2013    5   SHADE CHIPS 5   40
2013    6   FILLER SHADE    1   18
2013    6   SHADE CHIPS 4788    36790
2013    7   FILLER SHADE    1   18
2013    7   SHADE CHIPS 207 1600

I tried doing an initial group by year month, class, shipID, Internal Ship Line, Item, and take max of FG_Qty and Sum of RM_Qty. Then took that result and grouped it again, this time only grouping by year month, class and then summing FG_Qty and RM_Qty.

Note: Just doing a straight group by in one pass isn't working because the sum of FG_QTY is overstated since in the raw data the FG_QTY is replicated in multiple rows because of the join to the BOM Details table. So I need to only count the FG_Qty once per Internal SHipment Line Nbr.

Answer 1

Without knowing too much about your data, I would probably use a few CTEs to do this.

WITH RM AS (
 SELECT YEAR, MONTH, CLASS, SUM(RM_QTY) AS total_rm_qty
 FROM Shipment_Data SD
 JOIN BOM_Header BH ON 
  sd.id = bh.id
 JOIN BOM_Detail BD ON
  bh.id = bd.id
 GROUP BY YEAR, MONTH, CLASS
)
,FG AS (
 SELECT YEAR, MONTH, CLASS, SUM(FG_QTY) AS total_fg_qty
 FROM Shipment_Data SD
 GROUP BY YEAR, MONTH, CLASS
)
SELECT FG.YEAR,FG.MONTH,FG.CLASS, fg.total_rm_qty, rm.total_rm_qty
FROM FG
JOIN RM ON 
 FG.YEAR=RM.YEAR
 AND FG.MONTH=RM.MONTH
 AND FG.CLASS=RM.CLASS
ORDER BY
 FG.YEAR,
 FG.MONTH,
 FG.CLASS

Basically, you are doing a 1st pass to summarize the Raw Material Qty by year,month and class, then doing the same thing for the Finished Goods, but without joining to the BOM detail table. Once each has been summarized and grouped appropriately, you can join them at the appropriate level of aggregation.

Answer 2

You can do this by assigning a sequential number to the rows for each internal shipment line number (within a year and month and class, I think). Then, just sum up the values when the value is 1:

SELECT ShipYear, ShipMonth, CLASS, SUM(FG_ITEM), SUM(case when seqnum = 1 then FG_QTY end)
FROM (select p.*,
             row_number() over (partition by ShipYear, ShipMonth, Class,
                                             Internal_Shipment_Line_Num
                                order by (select NULL)) as seqnum
      from P
GROUP BY  ShipYear, ShipMonth, CLASS;

I'm not 100% sure that the partition clause is correct -- for instance, you might also want Shipment in it. But this should be a good start to solving your problem.

Answer 3

Something like this should do the trick

SELECT ShipYear, ShipMonth, CLASS, SUM(FG_ITEM), SUM(FG_QTY)
FROM P
GROUP BY  ShipYear, ShipMonth, CLASS

Hope this is helpfull

thanks

Answer 4

It seems like you just want to group by Year, Month, and Class - something like:

SELECT YEAR, MONTH, CLASS, SUM(RM_QTY), SUM(FG_QTY) FROM SHIPMENT_DATA
GROUP BY YEAR, MONTH, CLASS

If you don't have TOTAL_RM_QTY, you could do SUM(FG_QTY * RM_QTY_PER_FG_UNIT)