Brtdku

Edit: I'm using SQL Server 2014

I need to write a query to return only the number of rows where the sum of the quantity covers a quantity from a different table.

I'm working with two tables. An inventory table and a purchase history table. I want to return the rows from the purchase history table for a specific inventory item where the sum of the quantities in said rows covers the quantity on hand from the inventory table, ordered by newest purchase date to oldest.

Example:

Inventory table

-------------------

| SKU  | Quantity |

-------------------

| 1234 | 10       |

-------------------

Purchase history table

--------------------------------

| SKU  | Quantity | Date       |

--------------------------------

| 1234 | 5        | 2019-01-01 |

| 1234 | 3        | 2018-12-01 |

| 1234 | 9        | 2018-11-01 |

| 1234 | 4        | 2018-10-01 |

| 1234 | 12       | 2018-09-01 |

--------------------------------

This is the result I'm expecting

--------------------------------

| SKU  | Quantity | Date       |

--------------------------------

| 1234 | 5        | 2019-01-01 |

| 1234 | 3        | 2018-12-01 |

| 1234 | 9        | 2018-11-01 |

--------------------------------

Because 5 + 3 + 9 covers the 10 we have in stock.

Maybe I'm missing something simple, but I just can't think of how to get the results to stop at, in this specific case, the third record.

Also, I would really prefer to avoid using loops, if possible.

Any help would be greatly appreciated, because I just can't wrap my head around this one.

Another option is a CROSS APPLY

Example

Select B.* 

 From  Inventory A

 Cross Apply ( 

               Select *

                From  (

                      Select *,RB = sum([Quantity]) over (Partition By SKU Order by Date Desc) - Quantity

                       From  Purchase 

                       Where SKU=A.SKU

                      ) B1

                Where RB<=A.Quantity 

              ) B

you can do it with an INNER JOIN

when summing the rows, I use IIF to set the current row to zero in the sum, because I don't want to include that in the check

begin try drop table #Phistory end try begin catch end catch;

begin try drop table #inventory  end try begin catch end catch;

SELECT 1234 SKU,5 Quantity, cast('20190101' as date) [date] into #Phistory;

INSERT #Phistory (SKU, Quantity,[date]) VALUES (1234,3,'20181201'), (1234,9,'20181101'),(1234,4,'20181001'),(1234,12,'20180901'),(1235,3,'20181201'), (1235,9,'20181101'),(1235,4,'20181001'),(1235,12,'20180901'),(1235,500,'20180801'),(1235,50,'20180601');

select  1234 SKU, 10 quantity into #inventory;

insert #inventory values(1235,99);



SELECT p1.SKU, 

        p1.Quantity, 

        p1.[Date],

        SUM(p2.quantity) RunningTotal

                FROM #Phistory p1 

                    JOIN #PHistory p2 

                        ON p1.SKU = p2.sku and p2.date >= p1.date

                        GROUP BY p1.SKU, 

                                p1.Quantity, 

                                p1.[Date]

                        HAVING SUM(IIF(p1.date = p2.date , 0 , p2.quantity)) 

                                 <= (SELECT inv.quantity from #inventory inv where inv.sku = p1.sku) 

                        order by p1.sku,p1.date DESC;

I think you could probably do something like the following (untested):

SELECT ph.sku, ph.quantity, ph.date

FROM

(SELECT 

 purchasehistory.*, 

 SUM(quantity) OVER (PARTITION BY sku ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as tquantity

FROM purchasehistory) ph inner join inventory i

 on ph.sku = i.sku

WHERE

 ph.tquantity <= i.quantity

The idea here is to calculate the running sum in the inner query, then join with your inventory table. I've made an assumption with the ORDER BY date, but you could adjust that.

Edit: So based on the comment that you want the first N rows that use all your inventory (as opposed to the first N rows that don't exceed your inventory) I think you could do something like:

SELECT ph.sku, ph.quantity, ph.date

FROM

(SELECT 

 purchasehistory.*, 

 SUM(quantity) OVER (PARTITION BY sku ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as tquantity

FROM purchasehistory) ph inner join inventory i

 on ph.sku = i.sku

WHERE

 ph.tquantity < i.quantity

This is the same basic idea but the running sum is switched to only count previous rows (not include the current row) and the join uses < rather than <=. That way if the previous rows quantity didn't total to inventory yet, you will include the next row (even if that total now exceeds inventory).