By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,033 Members | 1,230 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,033 IT Pros & Developers. It's quick & easy.

More than Running Totals...

Uncle Dickie
P: 67
I've hit a wall and would appreciate any pointers in moving forward on some code...

I have some data in the following format:

ID, QtyIN, QtyOUT
1, 30, 0
13, 0, 40
16, 50, 0
32, 0, 40

Basically showing stock movements for a part, purchases and sales.

What I am trying to get is for every QtyOUT the ID for each QtyIN that is required to fulfil the sale. The ID number is always in chronological order so the lower the number the sooner it is in (or out).

So in the case of the above data I would want something along the lines of:

ID, QtyOUT, IDtoFulfil
13, 40, 1 and 16
32, 40, 16
Sep 14 '12 #1
Share this Question
Share on Google+
6 Replies


Rabbit
Expert Mod 10K+
P: 12,430
You'll need to create two running range queries. One for in and one for out. Then you join the two queries together to find the correct matches. Once you have that, you can use an XML query to string the results together.

A second option is to do it all with a cursor.
Sep 14 '12 #2

Uncle Dickie
P: 67
Thanks Rabbit!

I have something working almost as I want it by comparing running sums for in and out.
It's dog slow - takes about 15 minutes to run - but it is better than the 3 hours that it would take to get the information manually so for the time being I'm calling it good enough!

I have not used cursors before so when I am feeling a bit more adventurous I may revisit this report and see if I can make improvements in which case I may repost, but for the time being, thanks for your pointers.
Sep 18 '12 #3

Rabbit
Expert Mod 10K+
P: 12,430
If you want, you can post your query here and we can take a look at optimizing it.
Sep 18 '12 #4

Uncle Dickie
P: 67
Well that was a good way to get me to fix the problem!

I didn't want to look too stupid when I posted my code by missing something obvious so I spent a few minutes looking at it and it now runs in less than 1 second!

My problem was using a view that I had created. This view gets used multiple times and was what the running sums were worked out on. In an earlier attempt at getting the info something I was trying to do meant I couldn't use a temporary table (can't remember what it was) but that is now redundant.

Anyway, below is the code as it now stands and a quick summary of my thought process:

#StockProfile contains my transactional data - everything coming in and going out with a generated ID (myID) putting them in date and transaction type order (i.e due in before due out if they occur on the same date)

#temp1 has the running total of stock coming in by part

#temp2 has the running total of stock due out by part

#temp3 gets the first occurance when some stock is available and the first occurance when all stock is available to fulfil the outgoing order.

The final part just brings it all together and does my specific filtering.

Expand|Select|Wrap|Line Numbers
  1. SELECT    a.PartID
  2.         ,a.TransDate
  3.         ,a.QtyIN
  4.         ,a.QtyOUT
  5.         ,a.OrderNumber
  6.         ,a.LineNumber
  7.         ,row_number() OVER (ORDER BY a.Transdate, a.OrderNumber, a.LineNumber)    myID
  8. INTO    #StockProfile
  9. FROM    (
  10.         --Stock On Hand Levels
  11.         SELECT    p.PartID
  12.                 ,'01/01/1900'                    TransDate
  13.                 ,p.OnHandStockLevel                QtyIN
  14.                 ,0                                QtyOUT
  15.                 ,'On Hand'                        OrderNumber
  16.                 ,null                            LineNumber
  17.         FROM    [123_UK].Structure.Parts p        
  18.  
  19.         UNION ALL
  20.  
  21.         --Stock Due In 
  22.         SELECT    pod.PartID
  23.                 ,pod.ReceiptDate
  24.                 ,pod.QuantityPurchased - pod.QuantityReceived
  25.                 ,0
  26.                 ,'Purchase Orders'
  27.                 ,null
  28.         FROM    [123_UK].Purchase.PurchaseOrderDetails pod
  29.         WHERE    pod.ReceiptStatusID not in (3,4)
  30.  
  31.         UNION ALL
  32.  
  33.         --Stock Due Out to Works Orders
  34.         SELECT    wop.PartID
  35.                 ,wop.PlannedIssueDate
  36.                 ,0
  37.                 ,wop.PlannedIssueQuantity - wop.ActualIssueQuantity
  38.                 ,'Works Orders'
  39.                 ,null
  40.         FROM    [123_UK].Production.WorksOrderParts wop
  41.         JOIN    [123_UK].Production.WorksOrder wo ON wo.WorksOrderNumber = wop.WorksOrderNumber
  42.         WHERE    wop.WorksOrderIssueStatusCode not in (3,4)
  43.                 AND wo.IncompleteTransfers = 1
  44.                 AND wop.PlannedIssueDate is not null
  45.  
  46.         UNION ALL
  47.  
  48.         --Sales Orders Due Out
  49.         SELECT    sod.PartID
  50.                 ,sod.DespatchDate
  51.                 ,0
  52.                 ,sod.QuantityOrdered - sod.QuantityDespatched
  53.                 ,sod.SalesOrderNumber
  54.                 ,sod.LineNumber
  55.         FROM    [123_UK].Sales.SalesOrderDetails sod
  56.         JOIN    [123_UK].Sales.SalesOrders so ON so.SalesOrderNumber = sod.SalesOrderNumber
  57.         WHERE    sod.DespatchStatusID not in (3,4)
  58.     ) a
  59. GROUP BY    a.PartID
  60.             ,a.TransDate
  61.             ,a.QtyIN
  62.             ,a.QtyOUT
  63.             ,a.OrderNumber
  64.             ,a.LineNumber
  65.  
  66.  
  67. SELECT        myID
  68.             ,PartID
  69.             ,(
  70.                 SELECT        sum(t1.QtyIN)
  71.                 FROM        #StockProfile t1
  72.                 WHERE        t1.myID <= ccv.myID
  73.                 AND            t1.PartID = ccv.PartID
  74.                 GROUP BY    t1.PartID
  75.             ) RunningIN
  76. INTO        #temp1
  77. FROM        #StockProfile ccv
  78. WHERE        QtyIN > 0
  79.  
  80.  
  81. SELECT        ccv.myID
  82.             ,ccv.PartID
  83.             ,(
  84.                 SELECT        sum(t2.QtyOUT)
  85.                 FROM        #StockProfile t2
  86.                 WHERE        t2.myID <= ccv.myID
  87.                 AND            t2.PartID = ccv.PartID
  88.                 GROUP BY    t2.PartID
  89.             ) RunningOUT
  90. INTO        #temp2
  91. FROM        #StockProfile ccv
  92. WHERE        ccv.QtyOUT > 0
  93.  
  94.  
  95. SELECT        myID            myOutID
  96.             ,PartID
  97.             ,RunningOUT
  98.             ,(
  99.                 SELECT    MIN(myID)
  100.                 FROM    #temp1 t1
  101.                 WHERE    t1.PartID = t2.PartID
  102.                     AND    t1.RunningIN >= t2.RunningOUT
  103.             )                myInIDLast
  104.             ,(
  105.                 SELECT    Max(myID)
  106.                 FROM    #temp1 t1
  107.                 WHERE    t1.PartID = t2.PartID
  108.                     AND    t1.RunningIN < t2.RunningOUT
  109.             )                myInIDFirst
  110. INTO        #temp3
  111. FROM        #temp2 t2
  112.  
  113.  
  114. SELECT        ccv.OrderNumber
  115.             ,ccv.LineNumber
  116.             ,so.SecondSalesReference
  117.             ,p.PartNumber
  118.             ,convert(nvarchar(25),ccv.TransDate,106)    RequiredDate
  119.             ,ccv.QtyOUT
  120.             ,CASE
  121.                 WHEN ccv3.TransDate = '1900-01-01'
  122.                 THEN 'SoH'
  123.                 ELSE convert(nvarchar(25),ccv3.TransDate,106)
  124.             END    FirstDropDate
  125.             ,ccv3.QtyIN                                    FirstDropQty
  126.             ,CASE
  127.                 WHEN ccv2.TransDate = '1900-01-01'
  128.                 THEN 'SoH'
  129.                 ELSE convert(nvarchar(25),ccv2.TransDate,106)
  130.             END    LastDropDate
  131.             ,ccv2.QtyIN                                    LastDropQty
  132. FROM        #StockProfile ccv
  133. LEFT JOIN    #temp3 t3 ON t3.myOutID  = ccv.myID
  134. LEFT JOIN    #StockProfile ccv2 ON ccv2.myID = t3.myInIDLast
  135. LEFT JOIN    #temp3 t4 ON t4.myOutID  = ccv.myID
  136. LEFT JOIN    #StockProfile ccv3 ON ccv3.myID = t4.myInIDFirst
  137. JOIN        Structure.Parts p ON p.PartID = ccv.PartID
  138. JOIN        Sales.SalesOrders so ON so.SalesOrderNumber = ccv.OrderNumber
  139. WHERE        ccv.QtyOUT > 0
  140. AND            so.CustomerID = 13
  141. AND            ccv.TransDate < GETDATE()+90
  142. ORDER BY    ccv.TransDate
  143.  
  144.  
  145. DROP TABLE    #StockProfile
  146. DROP TABLE    #temp1
  147. DROP TABLE    #temp2
  148. DROP TABLE    #temp3
Sep 19 '12 #5

Rabbit
Expert Mod 10K+
P: 12,430
Glad you got it working! Good luck with the rest of your project!
Sep 19 '12 #6

ck9663
Expert 2.5K+
P: 2,878
Because this is mostly an RBAR processing, try to create the necessary indexes on your temp tables.

Good Luck!!!


~~ CK
Sep 19 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.