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

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 !

CodePudding user response：

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;