ask for sql 0908

Davis 61 Reputation points
2021-09-08T05:51:57.007+00:00

Hello experts,

I have one table with two columns(ID and Duration).

The value of duration is one number around 10.

I would like to list remaining records when the total of previous records is 100 or more than 100.

Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,579 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-09-08T06:04:39.407+00:00

    Hi @Davis ,

    Welcome to Microsoft Q&A!

    For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

    If we have 12 rows , and the total of first 10 rows is 100 or bigger than 100, need we still return current row?

    If no, please refer below example and check whether it is helpful to you.

    create table yourtable  
    (id int,  
    duration int)  
      
    insert into yourtable values  
    (1,11),  
    (2,8),  
    (3,9),  
    (4,12),  
    (5,14),  
    (6,9),  
    (7,13),  
    (8,10),  
    (9,12),  
    (10,8),  
    (11,10),  
    (12,12),  
    (13,11)  
      
    select * from yourtable  
    where id >(select min(id) from (select id,SUM(duration) OVER (ORDER BY ID ROWS UNBOUNDED PRECEDING) total from yourtable) a where total>=100)  
    

    Output:

    id	duration  
    11	10  
    12	12  
    13	11  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2021-09-08T06:16:42.747+00:00
    ;With cte As
    (Select ID, Duration, Sum(Duration) Over(Order By ID Rows Between Unbounded Preceding And 1 Preceding) As LagRunningTotal
    From YourTableName)
    Select ID, Duration
    From cte
    Where LagRunningTotal >= 100
    Order By ID;
    

    Tom