Recursive CTE Multi-Level Explosion in Exact MAX

 

 

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

;