I have a stored Procedure which works as coded but I need to add some functionality to it. Basically, what the stored proc does is, you specify via a parameter a final assembly item #. The stored proc looks at that, and returns all sub components of that end item. Essentially, a bill of material is what is returned.
What I need to do is add code to the stored procedure. For each item reported as part of the BOM, I want to look at all open/oustanding sales orders in the system and it's sub components to see if the same sub component exists in that given sales order and keep tally of the oustanding required quantities for said sub component and report in a additional column that is returned.
See attached the data that is returned via the current stored procedure. the highlighted column is the column I want to add.
Below is the stored procedure as it currently stands.
;with id as (select x.*
,ROW_NUMBER() over (order by [Name],[LineItemName]) as rn
from vwItems as x)
(select [Name],[LineItemName],0 as lvl,0 as depth
0)+':') as sortord, LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
lvl+1 As LVL, depth+1 As Depth
10)+':', LineItemQuantity As LineItemQty, QuantityOnHand As QtyOnHand
from cte as a
inner join id as b
select Items, depth, lvl, sortord, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand --* --items
] as Items,sortord,'B' as type, depth as depth, lvl as lvl, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
select [Name],sortord,'A', lvl, depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
,ROW_NUMBER() over (PARTITION by [Name]
order by sortord) as rn, lvl as lvl, depth As Depth, LineItemQty As LineItemQty, QtyOnHand As QtyOnHand
) as y
) as x
order by [type],sortord