Rollup Parent / Children pricing based on a lookup table

Probably not a unique problem, but it's unique to us.

We have a table with parent/children. The children can have multiple parents. The parents can have no children

Parent    Child
1000      1300
1000      1301
1001      1300
1001      1302
1002      1300
1002      1303
1003      NULL
1004      NULL

Our system is setup to accept orders by the parent part number. The children are then added after the parent (think of the children as options). The parents do not have a price if they have children. The parent will have a price if it doesn't have any children

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      0
0001          1300         C              1      500
0001          1301         C              1      350
0001          1301         NULL           1      400
0001          1003         NULL           2      500
0002          1002         P              1      0
0002          1300         C              1      200
0002          1303         C              1      100

What we're trying to do is rollup the parent/children pricing and accomplish this below

SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      850
0001          1301                        1      400
0001          1003                        1      400
0002          1002         P              1      300

Here is the sample dataset used

CREATE TABLE #Structure (
Parent INT
,Child INT )

INSERT INTO #Structure (Parent, Child)
SELECT 1000, 1300
UNION
SELECT 1000, 1301
UNION
SELECT 1001, 1300
UNION
SELECT 1001, 1302
UNION
SELECT 1002, 1300
UNION
SELECT 1002, 1303
UNION
SELECT 1003, NULL
UNION
SELECT 1004, NULL

CREATE TABLE #SalesOrder (
SalesOrder VARCHAR(4)
,StockCode INT
,ParentChild CHAR(1)
,Qty INT
,Price INT
,OrderLine INT)

INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price, OrderLine)
SELECT '0001', 1000, 'P', 1, 0, 1
UNION
SELECT '0001', 1300, 'C', 1, 500, 2
UNION
SELECT '0001', 1301, 'C', 1, 350, 3
UNION
SELECT '0001', 1301, NULL, 1, 400, 4
UNION
SELECT '0001', 1003, NULL, 1, 500, 5
UNION
SELECT '0002', 1002, 'P', 1, 0, 1
UNION
SELECT '0002', 1300, 'C', 1, 200, 2
UNION
SELECT '0002', 1303, 'C', 1, 100, 3

SELECT * FROM #Structure

SELECT * FROM #SalesOrder ORDER BY SalesOrder, OrderLine

DROP TABLE #SalesOrder
DROP TABLE #Structure

I'm guessing a recursive CTE is what I'm looking for? I've written them before, but not with a parent/child lookup. Hoping someone can push me in the right direction.

• Hey I knew your schema looked familiar, does this not still apply to your current question? (Can't believe it's been over a year already since I wrote that answer.) Based on the schema you've provided in your current question I don't think a recursive CTE is necessarily the best solution, but if you have an Items table (such as my aforementioned answer mentions) then it is possible to accomplish. But you may be able to accomplish your goals even without recursion. I'll try to take a stab at this when I get a chance.
– J.D.
Commented Dec 23, 2021 at 0:39
• In source data: 0001 1003 NULL 2 500. In desired output: 0001 1003 1 400. Why? Commented Dec 23, 2021 at 5:47
• Cross post on Stack Overflow stackoverflow.com/questions/70465767/… Commented Dec 24, 2021 at 6:41

SELECT t1.SalesOrder, t1.StockCode, 'P' ParentChild, t1.Qty, SUM(t2.Price) Price
FROM SalesOrder t1
JOIN SalesOrder t2 ON t1.SalesOrder = t2.SalesOrder
WHERE t1.ParentChild = 'P'
AND t2.ParentChild = 'C'
GROUP BY t1.SalesOrder, t1.StockCode, t1.Qty
UNION ALL
SELECT SalesOrder, StockCode, ParentChild, Qty, Price
FROM SalesOrder t1
WHERE ParentChild IS NULL
ORDER BY 1,2

?

https://sqlize.online/sql/mssql2017/4df6302b30f2b9022e2bb2863876a23e/