MSSQL LOOP for calculating remaining stock with oldest price in current available stock

Question

I am trying to figure out how to write a loop which would calculate a remaining portion of an individual items stock with oldes price...

e.g.

I have one Query

Select Document_Date, Quantity, Price FROM ArticlesTrafic
Where ArticleID = '605467' 
and DocumentTypeID = 'PRE'
and DocumentDate > '01-01-2020'

OUTPUT

Document_Date           Quantity   Price
2021-09-28 00:00:00.000 2          68,955405
2021-09-28 00:00:00.000 8          68,945184
2021-10-18 00:00:00.000 166        70,881881
2022-07-20 00:00:00.000 250        83,753749
2022-08-16 00:00:00.000 350        83,300471
2022-09-09 00:00:00.000 200        70

and another Query where I can pull the current Stock

Select stock from 
current_stock(1,GETDATE())
where ArticleID = '605467'

OUTPUT

stock 770

What I am trying to find out is how many items in that 770 available now are with the oldest price.

If we go back to the first table we can see that 200pcs were acquired for a price of 70, then 350 for a price of 83,3...etc

the result shold say that there are 220pcs in the currently available 770 which were acquired with 83,75 price

RESULT SAMPLE

stock    remaining_stock    oldest_price
770      220                83,753

Here is the same thin in Excel

It shows there are 16 items remaining with the oldest price in the current stock of 792

I Modified the solution posted by Charlieface

  SELECT Q.ArticleID, Q.Stock, Q.Remaining, Q.ProvisionUnit FROM
(SELECT
  at.*, cs.Stock,
  Remaining = cs.Stock - (at.RunningSum - at.TraficQuantityCredit),
  ROW_NUMBER() OVER (PARTITION BY at.ArticleID ORDER BY at.DocumentDate ASC) AS ID
FROM (
    SELECT
      at.ArticleID,
      at.DocumentDate,
      at.TraficQuantityCredit,
      at.ProvisionUnit,
      RunningSum = SUM(at.TraficQuantityCredit) OVER
          (PARTITION BY at.ArticleID ORDER BY at.DocumentDate DESC
           ROWS UNBOUNDED PRECEDING)
      FROM ArticlesTrafic at
      WHERE at.ArticleID IN ('605466', '605467')
      AND at.DocumentTypeID = 'PRE'
      AND at.DocumentDate > '01-01-2020'
) at
JOIN dbo.fnArticleStockPerDayTable(1, GETDATE()) cs ON cs.ArticleID = at.ArticleID
WHERE cs.Stock > at.RunningSum - at.TraficQuantityCredit
  AND cs.Stock <= at.RunningSum) AS Q
  WHERE Q.ID = 1

OUTPUT

ArticleID   Stock   Remaining   ProvisionUnit
605466      194     54          83,828542
605467      750     200         83,753749

Data is correct only problem is its very slow on a larger list of articles, will keep playing with it and optimize.

Thanks Again !

[Please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) — Thom A, Sep 12 '22 at 08:28
Why do you want to use a loop to achieve this? SQL is a set-based language; why not use a set-based solution? — Thom A, Sep 12 '22 at 08:29
I am sorry i figured a loop was required to achieve this result, i guess i should change the title then — Zvonimir Klenovic, Sep 12 '22 at 08:38
Sample data and expected results *as text* not images would help immensely — Charlieface, Sep 12 '22 at 09:17
I edited the post , only leaving one img from Excel... and also provided the desired output, thank you guys for heads up... — Zvonimir Klenovic, Sep 12 '22 at 09:42
For performance help, we need much more information. Table schema, index definitions, and please share the query plan via https://brentozar.com/pastetheplan. I suggest you create a *new* question for this, it's generally not appreciated to radically change a question after it's been answered — Charlieface, Sep 13 '22 at 09:27
I will try to separate sub selects using WITH see how fast it runs then.... — Zvonimir Klenovic, Sep 14 '22 at 05:29

score 1 · Accepted Answer · answered Sep 12 '22 at 10:53

You don't need a loop for this, you can use a window function. Loops are very rarely needed in SQL, and if you find yourself writing onoe you should question it.

You need to calculate the running sum of ArticlesTrafic.Quantity working backwards, then take the row which is the point where you go over the total Stock

SELECT
  at.*,
  Remaining = cs.Stock - (at.RunningSum - at.Quantity)
FROM (
    SELECT
      at.Document_Date,
      at.Quantity,
      at.Price,
      RunningSum = SUM(at.Quantity) OVER
          (PARTITION BY at.ArticleID ORDER BY at.DocumentDate DESC
           ROWS UNBOUNDED PRECEDING)
    FROM ArticlesTrafic at
    WHERE at.ArticleID = '605467' 
      AND at.DocumentTypeID = 'PRE'
      AND at.DocumentDate > '01-01-2020'
) at
JOIN current_stock(1, GETDATE()) cs ON cs.ArticleID = at.ArticleID
WHERE cs.Stock > at.RunningSum - at.Quantity
  AND cs.Stock <= at.RunningSum;

Thanks Charlie I modified the Query a bit, original result was an Output of 30 rows with only one correct row where I just had to select the row with the oldest date, I added another partition by I will edit the original post with the code — Zvonimir Klenovic, Sep 13 '22 at 06:50

MSSQL LOOP for calculating remaining stock with oldest price in current available stock

1 Answers1