0

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.

3
  • 1
    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?
    – Akina
    Commented Dec 23, 2021 at 5:47
  • Cross post on Stack Overflow stackoverflow.com/questions/70465767/… Commented Dec 24, 2021 at 6:41

1 Answer 1

0
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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.