Post reply

can i avoid cursor?

  • July 16, 2008 at 1:14 pm

    #184886

    How can I convert result set I into result set II ?

    DDL and sample data script provided.

    I am basically looking for set of admit and discharge records for a given client. If a client has more than one admit records before he discharges then I need max_id(row id) and min_id(row id) of admits. Else, in case of one admit record before discharge then max_id and min_id for admit record will be same. Same applies to discharge records.

    RESULT SET - I

    id client action

    --------------------------------------------

    1100admit

    2100admit

    3100admit

    4100discharge

    5100admit

    6100discharge

    7200admit

    8200discharge

    9300admit

    10300discharge

    11300discharge

    RESULT SET II

    min_idmax_idclient[action]

    -----------------------------------------------------------------------

    13100admit

    44100discharge

    55100admit

    66100discharge

    77200admit

    88200discharge

    99300admit

    1011300discharge

    create table #census(id int, client int, action varchar(20))

    insert into #census values(1,100,'admit')

    insert into #census values(2,100,'admit')

    insert into #census values(3,100,'admit')

    insert into #census values(4,100,'discharge')

    insert into #census values(5,100,'admit')

    insert into #census values(6,100,'discharge')

    insert into #census values(7,200,'admit')

    insert into #census values(8,200,'discharge')

    insert into #census values(9,300,'admit')

    insert into #census values(10,300,'discharge')

    insert into #census values(11,300,'discharge')

    select * from #census order by client, id

  • July 16, 2008 at 5:35 pm

    #844102

    This is right on the border line of me thinking this is some type of homework... please post a decent try!

    I will give you a hint, though... lookup GROUP BY.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 16, 2008 at 8:41 pm

    #844117

    I agree with Jeff, we're happy to help, but we'd like to see a try.

    GROUP BY, maybe include a HAVING clause for the COUNT() of records for a patient.

  • July 17, 2008 at 6:48 am

    #844333

    Thanks for your comments.

    I did solve above issue and got stuck with other. As I have tight production deadlines, I am going for cursor in a stored procedure.

  • July 17, 2008 at 12:15 pm

    #844583

    ALI (7/17/2008)

    Thanks for your comments.

    I did solve above issue and got stuck with other. As I have tight production deadlines, I am going for cursor in a stored procedure.

    See Steve? Remember I was talking about folks taking bad shortcuts just to get it done? Can't think of a setbased solution so that must justify the use of some form of RBAR, huh?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2008 at 12:32 pm

    #844592

    Jeff Moden (7/17/2008)

    ALI (7/17/2008)

    Thanks for your comments.

    I did solve above issue and got stuck with other. As I have tight production deadlines, I am going for cursor in a stored procedure.

    See Steve? Remember I was talking about folks taking bad shortcuts just to get it done? Can't think of a setbased solution so that must justify the use of some form of RBAR, huh?

    Oh right! Like that topic has ever come up here before!

    (It can be done pretty easily by adding a row number, and joining on row = row + 1 and status != status, of course. No triangle join, no min/max, no RBAR, just a simple self-join.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 17, 2008 at 12:55 pm

    #844616

    Already asked and answered here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=106744

    I wonder if people like this think it is fun to make twice as many people work for them for free?

    N 56°04'39.16"
    E 12°55'05.25"

  • July 17, 2008 at 1:01 pm

    #844622

    To explain more about the situation, above mentioned T-SQL issue is one among 10-15 complex data issues with in a single stored proc. I know I can go with T-SQL which involves 10-20 heavey updates/selects/self joins/outer apply/cross apply to come up with final result set. Myself being part of data management I need to consider performance and storage issues too. In this scenario cursor makes more sense. Current data volume is around 1 milion records and may go beyond 5milliion very soon. So how about 5 million records inserted to temp table then massage data with 10-20 heavy queries with self joins, outer apply, cross paly,....??? I say 10-120 complex queries and only one is mentioned in this case. When I use cursor I do straight insert into temp table and then get result set with simple if statements and without complex sqls.

    If you guys are intrested to look into entire stored proc data issues, we can take off of this site and I will be happy to provide you all information.

    My biggest issue is source table not designed to 3rd normal form. it is being used as flat file with 60 columns or so. Top of this weired business logic, as always it is the case:)

  • July 17, 2008 at 1:38 pm

    #844638

    If you're really going to consider performance and storage, then stop thinking Cursors or While loops and stop thinking about a 5 million row temp table. Make a permanent working table in a separate sandbox database that has the Recovery Mode set to simple like Temp DB does.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 17, 2008 at 2:48 pm

    #844672

    The reasons you give for using a cursor are pretty much the exact reason to NOT use one.

    In the specific problem given, I am willing to bet you a week's income, that I can make a set-based solution that will work faster and require less system resources than your cursor solution. I don't even need to see your cursor solution before I make the bet.

    This is most true on large, complex data sets: "Sets work better than cursors."

    Yes, a cursor might be needed in cleaning up data that isn't 3NF. Might. Generally not. In my experience, most anything a set-based solution can't clean up, needs human intervention and judgement anyway (people's names, for example, cannot be cleaned up by code, not fully).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply