CODEWITHSUNDEEP
sql-serversql-server-2014
BrownJenkin
BrownJenkin

Reputation: 3

Query to retrieve x number of rows based on quantity from another table

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.

Upvotes: 0

Views: 828

Answers (3)

EdmCoff
EdmCoff

Reputation: 3576

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).

Upvotes: 0

Cato
Cato

Reputation: 3701

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;

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 1

Related Questions