big task?

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.
ThanksIn 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
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JY" <jy1970us@.yahoo.com> wrote in message
news:O%bHf.5429$J%6.383634@.news20.bellglobal.com...
>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
>
>
>|||On Fri, 10 Feb 2006 21:31:09 -0500, JY wrote:

>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
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
Hugo Kornelis, SQL Server MVPsql