Show total quantities of all items used in a BOM

0

Please login or register to vote for this query.

(click on this box to dismiss)

Russian Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Russian language

CREATE TABLE #t(
    pid int,
    cid int,
    qty int
)

INSERT #t(pid, cid, qty) VALUES 
(2, 52, 20),
(2,	1,	45000),
(3,	53,	80),
(3,	1,	180000),
(4,	21,	2),
(4,	54,	2),
(4,	55,	1),
(4,	1,	506250),
(5,	1,	1),
(5,	38,	1),
(5,	28,	1),
(5,	1,	157500),
(6,	34,	2),
(6,	24,	2),
(6,	59,	1),
(6,	1,	472500),
(7,	8,	2),
(7,	60,	1),
(7,	1,	217500),
(8,	25,	2),
(8,	58,	1),
(8, 1, 157500),
(28, 1, 45000),
(28, 61, 20)

;WITH bom as (
  SELECT pid p,cid c, qty q, 0 bomlvl FROM #t WHERE pid=5 -- start id
  UNION ALL
  SELECT pid, cid, q*qty, bomlvl+1 FROM #t INNER JOIN bom ON c=pid
)
SELECT * from bom a WHERE NOT EXISTS (SELECT 1 FROM bom WHERE p=a.c and bomlvl >1) and bomlvl <=1

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results