question

Davis-8998 avatar image
0 Votes"
Davis-8998 asked Davis-8998 commented

ask for sql 0908

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's really to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.

1 Vote 1 ·

thank you, the question has been resolved.^-^

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered Davis-8998 commented

Hi @Davis-8998,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That's right,thanks!

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered Davis-8998 commented
 ;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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for providing another idea!

0 Votes 0 ·