Blanket Order Remainder

[dwg23]

Hello,
I am trying to write a query that will show blanket orders in the system to include Item, Quantity,Remaining Quantity and a few other things.
I have been able to show everything with the exception of the remaining quantity.

the Sales Order Table shows the original S.O. and the type as blanket, but when a item is sold a new sales order is created that has a column that references the original sales order. this new Sales Order creates a line in the Sales Order Line table for the Item and the quantity but I cannot figure out how to tie the original sales order to the one created when part of the blanket order is filled.

Here is the information of the blanket and a partial fill of the order creating a new Sales Order number.
SONumber Type Item BlanketSONumber
30846 BLNKET 69843
30847 RELEAS 69843 30846

If someone can get me from the original S.O. Number to where I can reference the new S.O. Number I should be good to go.

Thanks,
DWG23

 

[TamarGranor]

I don't exactly understand the problem. In particular, I don't entirely get the relationship between the blanket sales order and the later ones, but I think what you're looking for is a recursive CTE, something like:

WITH csrOrders (SONumber, Item, Qty, BlanketSONumber) AS

(SELECT SONumber, Item, Qty, SONumber 
    FROM SalesOrder
    WHERE BlanketSONumber = NULL
UNION ALL
SELECT SONumber, Item, Qty, BlanketSONumber
    FROM SalesOrder 
      JOIN csrOrders
        ON SalesOrder.BlanketSONumber = csrOrders.SONumber)

SELECT BlanketSONumber, Item, SUM(Qty)
  FROM csrOrders
  GROUP BY BlanketSONumber, Item

If I've misunderstood, maybe this will point you in the right direction anyway.

Tamar

 

[dwg23]

Tamar,
what I am trying to do is match the sales order number with the new sales order number and then take the new sales order number to the Sales Order Line Table so I can do a sum of all items sent against the original sales order number and subtract that total from the amount ordered leaving me the number of items still associated with the original Sales Order.
What I don't know how to do is get from the original sales order to the new one. I can't say for instance:

Select * from tbl.salesorder where salesorder = blanketsalesorder and then equate that to the new sales order.

although I think you may have pointed me in the right direction. (I hope)

Thanks,
DWG23

 

[dwg23]

Also the new sales order number goes to another table called soline table that has the actual totals in it.

Thanks,
DWG23

 

[dwg23]

OK,
I gave it some more thought and what I think I am trying to say is. This is not really a select statement just trying to get the info down.

Select All SONumber's from tblSOSO where tblSOSO.BlanketSO is Not Null and then be able to say.
Select Sum(tblSOLine.Quan)
Where "SONumbers not Null from above statment" = tblSOLine.sonumber
As 'Outstanding Total'

Hope this helps
DWG23

 

[TamarGranor]

From your pseudo-code, I still think a CTE will help. I don't have time to write the whole thing out right now, but think of it like this:

WITH YourCTE (fields) AS
  (Select All SONumber's from tblSOSO where tblSOSO.BlanketSO is Not Null)
Select Sum(tblSOLine.Quan) As 'Outstanding Total' 
  FROM tblSOLine
    JOIN YourCTE
      ON YourCTE.SONumber = tblSOLine.sonumber

Hope this gives you enough to get started. If not, holler and someone here should jump in.

Tamar

 

[dwg23]

Thanks! I am going out of town for a couple of days and when I get back with a fresh brain I will see how far I can get.
I will also look up what a CTE does as I am still confused as to how I can correlate one sonumber with a non-related sonumber.
for example sonumber 1234 is the blanket for 100 wigets.
A release is done against the blanket so for 10 wigets and a sonumber of 5678 is generated.
This is done in the soso table although no totals are in the soso table. now sonumber 5678 is also generated in the soline table which give the quantity for the 10 wigets.
I will work on it but i dont see how to get from the original sonumber to the release sonumber to then subtracting the released sonumber quantity from the original quantity.

Thanks,
DWG23

 

[TamarGranor]

If you can't get it when you get back, please post some code that creates sample tables (CREATE TABLE, INSERT INTO) and show the result you want, and then we can play with it.

Tamar

 

[dwg23]

Will do!
Thanks!