Please need help on looping through the dataset!!!

Question

Please need help on looping through the dataset!!!

moin6

Old Hand

Points: 374
More actions
October 31, 2008 at 3:15 pm

#200716

I have the following table with sample data:
Row#Quarter_NameWeek
-----------------------
14Q'05SEP26
2NULLOCT03
3NULLOCT10
41Q'06DEC26
5NULLJAN02
6NULLJAN09
7
82Q'06MAR27
9NULLAPR03
10NULLAPR10
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_NameWeek
-----------------------
14Q'05SEP26
24Q'05OCT03
34Q'05OCT10
41Q'06DEC26
51Q'06JAN02
61Q'06JAN09
7
82Q'06MAR27
92Q'06APR03
102Q'06APR10
Really appreciate all of your help!
Thanks.

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

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://www.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]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

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)

moin6 Old Hand Points: 374 More actions · Answer 3

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.

moin6 Old Hand Points: 374 More actions · Answer 4

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

Garadin One Orange Chip Points: 29613 More actions · Answer 5

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]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 6

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)

Garadin One Orange Chip Points: 29613 More actions · Answer 7

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]

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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://www.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.