I have 150 order from item code 24 I need to select the items id 1,2, 75p from 3 how can I do it in msSql server Query?

CodePudding user response：

You could use a rolling sum approach here:

WITH cte AS (
    SELECT *, SUM(Qty) OVER (ORDER BY ExpireDate) AS RollingQty
    FROM yourTable
),
cte2 AS (
    SELECT TOP 1 ExpireDate
    FROM cte
    WHERE 150 - RollingQty <= 0
    ORDER BY ExpireDate
)

SELECT *
FROM yourTable
WHERE ExpireDate <= (SELECT ExpireDate FROM cte2);

The logic above first computes a rolling sum of the quantity, as ordered by the expiry date. The second CTE then finds the first expiry date value for which the 150 quantity requirement is met. Then we select all records having expiry less than or equal to this date.

Here is a working demo.

CodePudding user response：

Try the following:

With cte AS (
    Select id, Itemcode, ExpireDate ,Qty,
    Case
        When Sum(Qty) OVER (Order By ExpireDate) <=150 
            Then Qty
        Else
            (150-Sum(Qty) OVER (Order By ExpireDate))   Qty
    End AS QtyInOrder
    From  MyTable Where itemcode=24 
)
Select 
id, Itemcode, ExpireDate ,Qty, QtyInOrder, (Qty-QtyInOrder) as RemainingInStore
From cte Where QtyInOrder>0 Order By ExpireDate 

The Sum(Qty) OVER (Order By ExpireDate) will return the accumulative sum of quantity over the expiry date.

At a given item row, if that accumulative sum is less than the total order quantity (150 in your example) then all of this item quantity will be included in the order, otherwise the included quantity will be (150-Sum(Qty) OVER (Order By ExpireDate)) Qty, i.e. for item3 it will be 150 - 175 100 = 75. To hold this value I added extra field in the cte called QtyInOrder, of course you may remove it from the select query.

The value of QtyInOrder for items not included in the total order will be less than or equal to zero, so I used Where QtyInOrder>0 when selecting from the cte.

See the result from dbfiddle.