As you can see from the attached file containing example data and the desired output, not only are there many levels of parent/child records, but you also have to make sure you're accessing the latest revision of the part number where the effectivedate <= today's date.
A partial BOM explosion would go like this (Partnum, Revisionnum):
TOP LEVEL 0: NC006000 C
LEVEL 1: NC00M021 6 (NO FURTHER BOM EXPLOSION ON THIS PART NUMBER)
LEVEL 1: NC00M022 5 (NO FURTHER BOM EXPLOSION ON THIS PART NUMBER)
LEVEL 1: NC006303 B
LEVEL 2: NC006301 B
LEVEL 2: NC006140 K
LEVEL 3: NC006141 K
...and so on.
I only need to perform this explosion for one part number at a time, in this case NC006000. This will always be a hardcoded part number because this query is not to be run by anybody but a programmer at this point.
I have very little SQL experience but am a good fast learner. I've been scouring many different books, forums, web sites, and any other source I can get my paws on for an answer for this. I've seen some good answers on EE but they usually are based on tables that (conveniently!) include the BOM levels. I somehow have to count them as I go and that is just over my head!
I found a snippet of recursive code that I've been playing with but have no idea how to add levels. Help???
WITH RPL AS (SELECT PARTMTL.PARTNUM, PARTMTL.MTLPARTNUM, PARTMTL.QTYPER
WHERE (PARTMTL.PARTNUM = 'NC006000')
SELECT CHILD.partnum, CHILD.mtlpartnum, CHILD.qtyper
FROM RPL AS PARENT INNER JOIN
dbo.partmtl AS CHILD ON PARENT.MTLPARTNUM = CHILD.partnum)
SELECT DISTINCT PARTNUM, MTLPARTNUM, QTYPER
FROM RPL AS RPL_1
ORDER BY PARTNUM, MTLPARTNUM, QTYPER