Post reply

Please need help on looping through the dataset!!!

  • October 31, 2008 at 3:15 pm

    #200716

    I have the following table with sample data:

    Row# Quarter_Name Week

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

    1 4Q'05 SEP26

    2 NULL OCT03

    3 NULL OCT10

    4 1Q'06 DEC26

    5 NULL JAN02

    6 NULL JAN09

    7

    8 2Q'06 MAR27

    9 NULL APR03

    10 NULL APR10

    Now I need to update the "NULL" values in "Quarter_Name" column with proper data. For example I need to update Row# 2 & 3 with 4Q'05....Row# 5 & 6 with 1Q'06.... Row# 9 & 10 with 2Q'06. I am not sure how to use "While Loop" or "Cursor" in T-SQL. Can any of you SQL folks send me the exact syntax? I've been searching the Google for Loop/cursor syntax since Wednesday and can't figure it out this specific task.

    The expected result should be following:

    Row# Quarter_Name Week

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

    1 4Q'05 SEP26

    2 4Q'05 OCT03

    3 4Q'05 OCT10

    4 1Q'06 DEC26

    5 1Q'06 JAN02

    6 1Q'06 JAN09

    7

    8 2Q'06 MAR27

    9 2Q'06 APR03

    10 2Q'06 APR10

    Really appreciate all of your help!

    Thanks.

  • October 31, 2008 at 3:52 pm

    #892613

    You don't need a loop, or a cursor. All you need to do is:

    1. Create a clustered index on your row# column. (It is most likely already there, but if it's not, create it). If you cannot modify your table, select your data into a temporary table and create a clustered index on that.

    2. Change the field names of this query to match yours, the clustered index in the bottom to match the name of yours and run this.

    3. Read the link in my signature for some tips on how to post data here for future questions. Had I had the information described in that article, this wouldn't be a guess, this would be tested code.

    [font="Courier New"]DECLARE @QN VARCHAR(20),

       @Row    INT

    UPDATE MyTable

    SET    @QN = Quarter_Name = ISNULL(Quarter_Name,@QN),

       @Row = Row

    FROM MyTable WITH (INDEX(ClusteredIndexName))

    [/font]

    For more information on this method, please view the following article.

    http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/

    (Lynn, I was gonna let you have this one, but you were offline!) :hehe:

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • October 31, 2008 at 6:44 pm

    #892637

    Garadin (10/31/2008)

    3. Read the link in my signature for some tips on how to post data here for future questions. Had I had the information described in that article, this wouldn't be a guess, this would be tested code.

    Man, I wish everyone would say that... we'd be able to help so much more! 🙂

    --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)
    Intro to Tally Tables and Functions

  • October 31, 2008 at 10:32 pm

    #892681

    Garadin,

    Thank you so much for the script. I will try to run this code in this weekend or first thing on Monday morning at office. Really really appreciate your response!!!

    Thanks.

  • November 3, 2008 at 10:56 am

    #893139

    Garadin,

    Your scripts worked like magic. Again, appreciate your response. I have another issue with the same data set but I will figure it out or add a new post in another forum. Thank you so much!!!

  • November 3, 2008 at 11:59 am

    #893171

    Thanks for the feedback moin6, glad we could help. Feel free to post your other questions as well.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 3, 2008 at 5:55 pm

    #893350

    It's amazing, even to me, how often that particular solution has been used, lately. Nice job, Seth. 🙂

    --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)
    Intro to Tally Tables and Functions

  • November 3, 2008 at 6:26 pm

    #893360

    Jeff Moden (11/3/2008)

    It's amazing, even to me, how often that particular solution has been used, lately. Nice job, Seth. 🙂

    When I read (and then later fully comprehended) the way the method worked, it opened up a whole new train of thought for how datasets could be updated. I never would have thought to set variables in an Update statement before that. Thanks again for the articule Jeff.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • November 3, 2008 at 7:54 pm

    #893380

    Awesome to know... thanks for the compliment, Seth. I really appreciate it. Heh... I also appreciate you getting to all of these before me! Makes my life a whole lot easier! 🙂

    --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)
    Intro to Tally Tables and Functions

  • November 3, 2008 at 8:50 pm

    #893390

    Garadin (10/31/2008)

    You don't need a loop, or a cursor. All you need to do is:

    1. Create a clustered index on your row# column. (It is most likely already there, but if it's not, create it). If you cannot modify your table, select your data into a temporary table and create a clustered index on that.

    2. Change the field names of this query to match yours, the clustered index in the bottom to match the name of yours and run this.

    3. Read the link in my signature for some tips on how to post data here for future questions. Had I had the information described in that article, this wouldn't be a guess, this would be tested code.

    [font="Courier New"]DECLARE @QN VARCHAR(20),

       @Row    INT

    UPDATE MyTable

    SET    @QN = Quarter_Name = ISNULL(Quarter_Name,@QN),

       @Row = Row

    FROM MyTable WITH (INDEX(ClusteredIndexName))

    [/font]

    For more information on this method, please view the following article.

    http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/

    (Lynn, I was gonna let you have this one, but you were offline!) :hehe:

    Well, Sometimes I have to work, or travel home for the evening.

    I wonder what the other issue with the dat will be.

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

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