Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    42

    Cool 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

    thnks in adv.,
    chakri

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Would the answer in this case be 650?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    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. #4
    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).
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

    So - why two answers?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    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. #7
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    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. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •