Below is a recursive CTE that does a multi-level explosion for the current effective date.
;with mlBOM
as
(
select PARPRT_02, COMPRT_02
, QTYPER_02, EFFDTE_02
from Product_Structure
WHERE PARPRT_02 = 'ENTER PARENT PART'
union all -- CTE recursion
select n.PARPRT_02, n.COMPRT_02
, n.QTYPER_02, N.EFFDTE_02
from Product_Structure n
inner join mlBOM c on c.COMPRT_02 = n.PARPRT_02
)
-- final select aggregating values
-- displays unique PARPRT_02 - COMPRT_02 combinations
select PARPRT_02, COMPRT_02, QTYPER_02, EFFDTE_02
from mlBOM
WHERE EFFDTE_02 <= GETDATE() OR EFFDTE_02 IS NULL
group by PARPRT_02, COMPRT_02, QTYPER_02, EFFDTE_02
;
