I’m struggling to wrap my head around this one. I’m sure someone will simplify it.
We have a parent/child relationship that I’m needing to roll-up based on sold quantities and sales dollar amounts.
We have a table that stores the parent and their children. Looks like this:
Parent Component 1000 1300 1000 1301
When an order is placed, our users order a part number of 1000, then add in line items 1300, 1301 as options. Parent parts do not have a sales value assigned to them, but components do have a value. So what I’m trying to do is roll up everything to the parent level (sales and quantities) on a sales order.
Here’s how a sales order would look:
SalesOrder StockCode ParentChild Qty Price 0001 1000 P 1 0 0001 1300 C 1 500 0001 1301 C 1 350 0001 1301 1 400
As you can see above, we can have components (
StockCode 1301) in here without parents (think of these as replacement parts). If the sales order line does not have a
ParentChild value, they need to be excluded from the roll-up.
Here’s how I’m wanting this data to look. A parent/child should only have a Qty of the parent.
SalesOrder StockCode ParentChild Qty Price 0001 1000 P 1 850 0001 1301 1 400
I’m guessing that STUFF/FOR XML PATH would be a good starting point?
Here is the test data:
CREATE TABLE #Structure ( Parent INT ,Component INT ) INSERT INTO #Structure (Parent, Component) SELECT 1000, 1300 UNION SELECT 1000, 1301 CREATE TABLE #SalesOrder ( SalesOrder VARCHAR(4) ,StockCode INT ,ParentChild CHAR(1) ,Qty INT ,Price INT ) INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price) SELECT '0001', 1000, 'P', 1, 0 UNION SELECT '0001', 1300, 'C', 1, 500 UNION SELECT '0001', 1301, 'C', 1, 350 UNION SELECT '0001', 1301, NULL, 1, 400 SELECT * FROM #Structure SELECT * FROM #SalesOrder DROP TABLE #SalesOrder DROP TABLE #Structure