i have a table with 3 columns:
duration (int)
startdatetime (bigint)
userid (int)

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000.

how can i do that? i can only think of a cursor solution.

Thanks

In the future, instead of columnname (datatype) if you could give a real
table structure and data it would be easier to get a proper solution.  I get
the feeling that this is a bit more complex that you are letting on, so you
might have to rework the code where I a TOP to include max values and
groupings, but based on the table you gave:

drop table test
go
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into test
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1

go

select top 1 *
from   (    select *,
     (select sum(startdatetime)
     from test as t2
     where t2.startDateTime <= test.startDateTime) as cumulativeDuration
   from test) as sums
where cumulativeDuration > 1000

Returns:

duration    startdatetime        userid      cumulativeDuration
----------- -------------------- ----------- --------------------
500         600                  1           1100


Show quoteHide quote

On Fri, 10 Feb 2006 21:31:09 -0500, JY wrote:

Hi JY,

Here's an alternative solution, based on the table and data created and
posted by Louis Davidson:

SELECT TOP 1 a.duration, a.startdatetime, a.userid,
             SUM(b.duration) AS cumulativeDuration
FROM         test AS a
INNER JOIN   test AS b
      ON     b.startdatetime <= a.startdatetime
GROUP BY     a.duration, a.startdatetime, a.userid
HAVING       SUM(b.duration) > 1000
ORDER BY     a.startdatetime

Why is this statement blocking...
NESTED SQL QUESTION and aggregate function
CLR Triggers and inserted table
SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?