0

I have a data like this:

Id  Qty   Price
----------------
1    5    200
2   20    230
3   40    180
4   10    200

I need to get the rows based on a quantity limit.

For example, using Qty = 30, I should get this output: 

Id  Qty   Price
----------------
1    5    200
2   20    230
3   40    180
h3n
  • 5,142
  • 9
  • 46
  • 76
  • 2
    Something like `DECLARE @Qty INT = 30; SELECT ID, Qty, Price FROM (SELECT *, RunningPrevQty = ISNULL(SUM(Qty) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) FROM myTable) AS T WHERE RunningPrevQty < @Qty;` (Assuming SQL Server 2012+... For versions before that, you would need to use another method to get a running total, such as a subquery/apply or some other method to achieve that.) – ZLK Nov 05 '18 at 04:39
  • I think you can find your answer in this link: [a link](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) – Raha Shafaei Nov 05 '18 at 05:26
  • I think you can find your answer in this link: https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum – Raha Shafaei Nov 05 '18 at 05:31
  • could you please describe your logic of this output – Zaynul Abadin Tuhin Nov 05 '18 at 05:35
  • For `Qty = 30` I would expect to see the 2nd and 4th rows - where the sum of their quantity equals 30 exactly - Unless there's another reason to use the order of the `id` column. – Zohar Peled Nov 05 '18 at 06:32
  • 1
    @ZLK 1. I think you better post an answer, not a comment with this content. 2. Sum...over was supported in 2008 version as well. – Zohar Peled Nov 05 '18 at 06:33
  • @ZLK sql works. But Im worried that the inner query retrieves all the data first before filtering it. – h3n Nov 05 '18 at 06:47
  • SQL is a language where you tell the system *what you want*, not *how to do it*. The system is free to reorder operations as it sees fit in order to satisfy the query as best it can. Which is to say, whether or not it *does* retrieve all rows for the subquery first is not something under your control anyway. Hopefully, it doesn't in fact do that. – Damien_The_Unbeliever Nov 05 '18 at 07:16
  • 1
    @ZoharPeled just FYI, in 2008, aggregate window functions did not support ORDER BY in the OVER clause (only PARTITION BY). – ZLK Nov 05 '18 at 21:07

2 Answers2

0

You can try a combination of the analytical version of SUM and LAG function to solve your problem.

Test data

drop table if exists #test;

create table #test (
    Id int,
    Qty int,
    Price numeric(10,2)
)

insert into #test ( Id, Qty, Price )
    values ( 1 ,  5 , 200 ) , ( 2 , 20 , 230 ) , ( 3 , 40 , 180 ) , ( 4 , 10  , 200 );

Solution

with 
cte1 as ( select Id, Qty, Price, SUM(Qty) OVER(ORDER BY ID) as Total from #test ),
cte2 as ( select t1.*,COALESCE(LAG(Total) OVER (ORDER BY ID),0) as PrevTotal from cte1 t1 )
select Id, Qty, Price from cte2 where PrevTotal <= 30;

Result

enter image description here

Jacek Wróbel
  • 1,172
  • 10
  • 17
0

I would simply do:

select t.*
from (select t.*,
             sum(qty) over (order by id) as running_qty
      from t
     ) t
where running_qty - qty < 30;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786