# Thread: query without using cursor

1. Registered User
Join Date
Feb 2006
Posts
42

## Unanswered: query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

chakri

2. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Would the answer in this case be 650?

3. Registered User
Join Date
Feb 2006
Posts
42
it will be 650 and 700 according to user id
Last edited by chakri; 05-02-06 at 07:27.

4. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).

5. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Ok - you edited your typo while I was posting

6. Registered User
Join Date
Feb 2006
Posts
42
ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.

7. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.co...ly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH

8. Registered User
Join Date
Feb 2006
Posts
42
i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.

9. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579