Cursor

  • Hi

    I have a Unique need, even with the cost of performance using Cursors , i could not find any tools to achieve my goal without using Cursor. Could anyone just show me direction?

    Here i have attached a table which show statusid( 1=open, 2=Close), Prd=Product, id is the order how the record appears on the table

    Scenario: i would like to retrieve row by row data checking if statusid =1 and until it sees

    another status id=2 it should consider only one event for all those record and

    insert into one table or put into variable( doesnot matter) . The next record with statusid=1 after that last Statusid=2 should be consider new set until again it hits the next status id =2 .So, i am looking at the block by block data retreival order by id(not the status id , they are different)

    In simple form , i would like each block of data sequentially which starts with statusid=1 and ends on

    status id =2 and after that again move to the next set following the same pattern

    May be cursor can be used? , i am not sure, fairly new to Cursor

    Any help will be highly appreciated

    id creation date Prd statusid

    138:16.7A100:00.019:36.42009Jul

    248:50.0A100:00.019:36.42009Jul

    352:18.0A100:00.019:36.42009Jul

    457:28.9A100:00.019:36.42009Jul

    501:37.3A100:00.019:36.42009Jul

    606:31.1A100:00.019:36.42009Jul

    708:01.8B100:00.019:36.42009Jul

    811:11.6B1 00:00.019:36.42009Jul

    949:31.3B100:00.019:36.42009Jul

    1051:31.2B100:00.019:36.42009Jul

    1151:34.5B1 00:00.019:36.42009Jul

    1257:48.9B1 00:00.019:36.42009Jul

    1305:21.4B200:00.019:36.42009Jul

    1401:20.7B2 00:00.019:36.42009Jul

    1525:03.6A100:00.019:36.42009Jul

    1652:58.3A1 :00.019:36.42009Jul

    1712:02.6A100:00.019:36.42009Jul

    1849:51.4A100:00.019:36.42009Jul

    1950:01.3A1 :00.019:36.42009Jul

    2053:19.9A100:00.019:36.42009Jul

    2119:36.4A2 00:00.019:36.42009Jul

    Thanks

    Simon

    Here i have scripted the table and the data such that it is rerunnable

    Thanks for your suggestion, Jeff

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

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

    IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

    DROP TABLE #mytable

    CREATE TABLE #mytable

    ( ID INT IDENTITY(1,1) ,

    DateValue DATETIME, Prd nvarchar(250), Statusid INT )

    SET IDENTITY_INSERT #mytable ON

    INSERT INTO #mytable

    (ID, DateValue,Prd, Statusid)

    SELECT '1','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '2','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '3','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '4','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL

    SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL

    SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL

    SELECT '5','Oct 17 2007 12:00AM','B','1' UNION ALL

    SELECT '6','Oct 17 2007 12:00AM','B','2' UNION ALL

    SELECT '6','Oct 17 2007 12:00AM','B','2' UNION ALL

    SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '7','Oct 17 2007 12:00AM','A','1' UNION ALL

    SELECT '8','Oct 17 2007 12:00AM','A','2'

    SET IDENTITY_INSERT #mytable OFF

  • Hi Simon,

    You'd probably get a faster, better response if you put your data in a ready to use/test format. Take a look at the link in my signature line below. 😉

    --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)

  • You can avoid a cursor.

    Create a temp table with the ID, Prod, Date and Status from the main table, and two extra columns, SetStart and SetEnd, both bit data type.

    Then run this on the temp table:

    ;with CTE (Row, Date, Prd, StatusID, SetFirst, SetLast) as

    (select row_number() over (partition by Prd order by Date),

    Date, Prd, StatusID, SetFirst, SetLast

    from #TempTable)

    update C1

    set

    SetFirst =

    case

    when C2.row is not null then 1

    else 0

    end,

    SetLast =

    case

    when C3.row is not null then 1

    else 0

    end

    from CTE C1

    left outer join CTE C2 -- The next row is a 2

    on C1.Prd = C2.Prd

    and C1.Row = C2.Row-1

    and C1.StatusID = 1

    and C2.StatusID = 2

    left outer join CTE C3 -- The prior row is a 2

    on C1.Prd = C3.Prd

    and C1.Row = C3.Row+1

    and C1.StatusID = 2

    and C3.StatusID = 1

    That should give you what you need. It may need refining, since I don't have your table structure and data to test it on, but it should be okay.

    - 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

  • simon phoenix (8/26/2008)


    Here i have scripted the table and the data such that it is rerunnable

    Thanks for your suggestion, Jeff

    Good... Here's one more suggestion... don't edit your posts on something like this. People who have already seen the "bad" post won't necessarily look at an edited post because editing doesn't change the submitted date to show an update. Always make a new post on the thread. It will also cause the thread to appear on the "recent posts" selection folks like me use with a new datetime effectively making the post appear near the beginning as a new post would.

    Don't get carried away with such "bumps" either... 😉

    --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)

  • Simon,

    Now that Gus has replied, it's very good forum etiquette/good practice to let him know if it worked or not. If it didn't work, explain why with some good detail and if any error messages show up, be sure to include those.

    Heh.. NO Gus... not saying your code has any errors... just trying to help out a newbie. 😀

    --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)

  • Hi

    Jeff,Gsquared

    Thanks for giving me the best suggestions that helped Gsquared to provide me the outstanding logic for my task. It was my first time posting and i was quite skeptical about it, i am amazed there are people like jeff and Gsquared who actually get into the problem , no matter how long and tedious it was! I am impressed. Gsquared , thanks a million

    For 'Gsquared'

    You have understand my requirment, so it raises my expectation that you could help me nail this down.

    your script and logic is outstanding and it was able to tag SetFirst and SetLast with 1. But the problem is that i

    I would like to update the temp table like this:

    Every time it sees different 'Prd' and different status id it should update accordingly

    id 1, if Prd =A, statusid=1, then update setfirst=1 and setlast=1

    id 2, if Prd=B, status id=1, then update setfirst=2 and setlast=1

    id 3, if Prd=B, statusid =2, then update setfirst=1 and setlast=1

    id 4, if Prd=B, statusid=2, then update setfirst=1 and setlast=2

    id 5, if Prd=A, statusid=1, then update setfirst=3 and setlast=1

    id 6, if Prd=A, statusid=2, then update setfirst=1 and setlast=3

    id 7, if Prd=B, statusid=1, then update setfirst=4 and setlast=1

    id 8, if Prd=B, statusid=2, then update setfirs=1 and setlast=4

    Basically i am trying to distinguish the each block of data which starts from one 'Prd' and statusid=1 and change to different 'Prd' and change to statusid=2 and might again change back to different 'Prd'

    and different status orderby id

    Business logic : i am trying to see how the case was open on Product Support centre when customer call for troubleshooting . First what 'Prd' name they open the case and as time goes by how the case was handled and how many time the 'Prd' name was changed and how many times it was open and closed under different 'Prd' name

    Here i have also attached the data on excel file, hope it might provide some insight

    Millions of Thanks Gsquared

    Thanks

    Simon

  • Thanks for the feedback, Simon... Gus is one of those tenacious posters... you landed a good one;)

    --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)

  • Jeff Moden (8/27/2008)


    Simon,

    Now that Gus has replied, it's very good forum etiquette/good practice to let him know if it worked or not. If it didn't work, explain why with some good detail and if any error messages show up, be sure to include those.

    Heh.. NO Gus... not saying your code has any errors... just trying to help out a newbie. 😀

    Jeff, half my code posts aren't tested, because I don't have the data to test them or don't have the time. Trust me, those ones quite regularly have a few errors in them. I think I even had a backwards inequality relationship in one that made it into complete nonsense. (The other half, I have the data and time to test, and those ones work as written.)

    - 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

  • The idea of the "Set First" and "Set Last" columns is that they are the first and last records in each set.

    Once you have those, what you need to do is select the ones where SetFirst = 1, and assign a row_number to them, and do the same with the SetEnd = 1, then join those together and get everything in between them.

    Something like this (adding a "SetID" column, data type = int, and "SetSequence", also int):

    ;with

    Sets1 (ID, SetID1) as

    (select id, row_number() over (partition by prd order by date)

    from #TempTable

    where SetFirst = 1),

    Sets2 (ID, SetID2) as

    (select id, row_number() over (partition by prd order by date)

    from #TempTable

    where SetLast = 1)

    update TempTable

    set SetID = SetID1

    from Sets1

    inner join Sets2

    on Sets1.SetID1 = Sets2.SetID2

    inner join #TempTable TempTable

    on TempTable.ID between SetID1 and SetID2;

    with

    SetSeq (ID, SetSequence) as

    (select ID,

    row_number() over (partition by SetID order by date)

    from #TempTable)

    update TempTable

    set SetSequence = SetSeq.SetSequence

    from #TempTable TempTable

    inner join SetSeq

    on TempTable.ID = SetSeq.ID

    The first query breaks them up into sets, based on the SetStart and SetEnd columns and the Date column.

    The second query then takes each set and gives it a sequence number within the set.

    Is that what you're looking for? Is it clear enough?

    - 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

  • Hi

    Gsquared

    Your first query for SetFirst and Set last was tagging the setfirst to the record just before the setlast (stausid=2) but i was looking for it to tag for the very first record it found order by id as setfirst=1 and again setfirst =1 immediately after statusid=2

    statusid setfirst setlast

    1 1 0

    1 0 0

    1 0 0

    2 0 1

    1 1 0

    1 0 0

    2 0 1

    Righ now your query is tagging the setfirst=1 one record before it sees statusid=2, that do not represent the section, section is where it first starts and closed

    One more question on your second query, is that id same as column 'Row' from the previous #temptable , if not it do not exist in our previous #temptable to select?

    Sets1 (ID, SetID1) as

    (select id,

    Again , GSquared Tons of thanks for spending your valuable , SQL guru time for my tedious task

    Thanks

    Simon

  • In the first query, change:

    and C1.Row = C3.Row+1

    to:

    and C1.Row = C3.Row-1

    See if that gets you what you need.

    On the second query, I often call the row_number column in a CTE "Row". It's generic for me. Doesn't matter what you call it.

    - 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

  • I misunderstood your question about "Row". No, the ID column is the ID from the original table. You'll need to add that to the temp table.

    - 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

  • Hi

    Gsquared

    Setfirst still missing the tag even after chaning the first query and even SetLast is taging on some place and not in other!

    Thanks

    Simon

  • GSquared (8/28/2008)


    Jeff, half my code posts aren't tested, because I don't have the data to test them or don't have the time. Trust me, those ones quite regularly have a few errors in them. I think I even had a backwards inequality relationship in one that made it into complete nonsense. (The other half, I have the data and time to test, and those ones work as written.)

    Guess I have to say that, knowing that, I'm even more impressed with your work. 🙂 It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.

    --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)

  • Jeff Moden (8/28/2008)


    It's also part of the reason I wrote that forum ettiquette article on how to provide actual test data so that good folks like yourself don't have to spend so much time creating data and can get right to trying to solve the problem, instead.

    Man, I think that I linked to that article like 20 times today. Now I know why you and Jack put it into your signature!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 15 posts - 1 through 15 (of 48 total)

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