[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

ehnancing a stored procedure to include an additional column

I have a stored procedure, pasted below which runs in sql server and pulls data from 3 different tables.  The stored procedure returns a Bill of Material.  (You input the top level assembly part # and it returns all of the sub assemblies and components required to make the top level assembly.  I have attached a spreadsheet which depicts the output when you run the top level assembly # US9000.  

I need a way of adding all of the lineitemqty's that start with LABOR and putting the total in a column called cumulitivetime and having that value represented on the row for US9000.  I then need to also put the cumulitivetime for each subcomponent that makes up US9000.  I have color coded the spreadsheet to show what I am hoping to accomplish.

ALTER PROCEDURE [dbo].[BOM]
      -- Add the parameters for the stored procedure here
@ParentItem varchar(200)
AS
BEGIN
with recursive_LineItems as (
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, ISNULL(QtyOnOrder,0) QtyOnOrder
  FROM Items
 INNER JOIN ItemLineItems
         ON ItemLineItems.Name = Items.Name
  LEFT JOIN (SELECT ItemName, SUM(ItemQuantity) QtyOnOrder FROM tblSalesOrders GROUP BY ItemName)so
         ON Items.Name = so.ItemName
UNION ALL
SELECT ItemLineItems.Name, ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity, r.QtyOnOrder
  FROM recursive_LineItems r
 INNER JOIN ItemLineItems
         ON r.LineItemName = ItemLineItems.Name
), LineItem_w_OrderQty as (
SELECT LineItemName, SUM(LineItemQuantity*QtyOnOrder) as TotQtyOnOrder
  FROM recursive_LineItems
 GROUP BY LineItemName
), id as (
SELECT Items.Description, Items.QuantityOnHand, Items.QuantityOnOrder, Items.QuantityOnSalesOrder, ItemLineItems.Name,
       ItemLineItems.LineItemName, ItemLineItems.LineItemQuantity,
       isnull(wQ.TotQtyOnOrder,0) TotQtyOnOrder
     , ROW_NUMBER() over (order by Items.Name, ItemLineItems.LineItemName) as rn
  FROM ItemLineItems
 INNER JOIN Items ON ItemLineItems.LineItemName = Items.Name
  LEFT JOIN LineItem_w_OrderQty wQ ON ItemLineItems.LineItemName = wQ.LineItemName
), cte as (
select [Name]
      , [LineItemName]
      , 0 as lvl
      , 0 as depth
      , convert(varchar(max),RIGHT('0000000000'+convert(varchar(10),rn),10)+':') as sortord
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , TotQtyOnOrder
   from id
  where [Name]='US9000'
 union  all
 select b.[Name]
      , b.[LineItemName]
      , lvl+1 As LVL
      , depth+1 As Depth
      , a.sortord+RIGHT('0000000000'+convert(varchar(10),b.rn),10)+':'
      , LineItemQuantity As LineItemQty
      , QuantityOnHand As QtyOnHand
      , b.TotQtyOnOrder
   from cte as a
  inner join id as b
     on b.[Name]=a.[LineItemName]
)  
select Items
     , depth
     , lvl
     , sortord
     , LineItemQty As LineItemQty
     , QtyOnHand As QtyOnHand --* --items
     , TotQtyOnOrder
  from (select replicate('-',depth+1)+[LineItemName] as Items
             , sortord
             , 'B' as type
             , depth as depth
             , lvl as lvl
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from cte
        union all
        select [Name]
             , sortord
             , 'A'
             , lvl
             , depth
             , LineItemQty As LineItemQty
             , QtyOnHand As QtyOnHand
             , TotQtyOnOrder
          from (select [Name]
                     , sortord
                     , ROW_NUMBER() over (PARTITION by [Name] order by sortord) as rn
                     , lvl as lvl
                     , depth As Depth
                     , LineItemQty As LineItemQty
                     , QtyOnHand As QtyOnHand
                     , TotQtyOnOrder
                  from cte
                 where depth=0 ) as y
         where rn=1 ) as x
order by [type],sortord;
0
jwebster77
Asked:
jwebster77
  • 4
  • 2
1 Solution
 
lwadwellCommented:
no spreadsheet.
0
 
jwebster77Author Commented:
See Attached
BOM.xlsx
0
 
lwadwellCommented:
Normally I would try and do things in the one SQL ... as a rule.  There are exceptions, this is one ... the existing SELECT was getting too complex for this so I have used a temp table with some additional UPDATE statement to achieve what I hope you want.
See attached.
Q-27857768.sql
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
jwebster77Author Commented:
Thanks lwadwell.  I am trying that now!
0
 
jwebster77Author Commented:
Hi lwadwell, I wonder.  Would it be possible in this query to have it spit out the total like it is doing now, but have it also spit out in another column, the sales order due dates for the sales orders it picked up where the component is required.  So, in the query above you did it looks for any sales orders that are open where the component exists on those sales orders to.  I would really like to have a nother column that lists the sales order number and due date.  So something like this,

order#1-01/01/2013,order#2-01/05/2014,order#3-01/07/15, etc., etc.
0
 
jwebster77Author Commented:
Hi lwadwell.  I posted a question about adding somethign to this stored procedure which you helped me with and one of the persons who responded recommended I come back to you to see if you can help me add a column!

Would it be possible in this query to have it spit out the total like it is doing now, but have it also spit out in another column, the sales order due dates for the sales orders it picked up where the component is required.  So, in the query above you did it looks for any sales orders that are open where the component exists on those sales orders to.  I would really like to have a nother column that lists the sales order number and due date.  So something like this,

order#1-01/01/2013,order#2-01/05/2014,order#3-01/07/15, etc., etc.

Any thoughts on this?  Thanks!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now