Looping through data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Looping through data

Author

Topic

spikr77
Starting Member

6 Posts

Posted - 2009-11-12 : 09:49:53

Hello all, I am a newbie and have a questions about getting data out of a table. I have a situation where a record can have multiple telephone numbers related to it. I would like to be able to loop through the phonenumber table, look at the data it is grabbing and do something with it. For example, I am the employee and I have a home phone number, a cell phone number, and an email address (all are stored in the same table with a type specific to the type of phone number) I then want to loop through the phone numbers for me and first grab the home number and stick it into a new table as home_phone, I would then like to loop through and do the same with the cell phone and email address and add them to the new table as cell_number and email_address. I've spent a few hours searching but my biggest issue is I'm really not sure what I should be looking for. Any help would be greatly appreciated. Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-12 : 09:56:36

Show us the structure of source and destination table.
Give us some sample data.
Show us - in relation to the sample data - your wanted result.

I'm sure we can help.

No, you're never too old to Yak'n'Roll if you're too young to die.

spikr77
Starting Member

6 Posts

Posted - 2009-11-12 : 13:20:13

Hi WebFred. The table I want to pull data from is similar to this:

Person_ID Telecom_Type_Name Start_Date End_Date Phone_No Email_Addres
01234567 Home Date Date 1234567 null
01234567 Internet Date Date null Email Address

Sorry it's not perfect.. lol. And the table I want to put the data into is similar to this:

Person_Id Home_Phone1 Home_Phone2 Cell_Phone Email_Address
01234567 1234567 null null Email Address

So basically I want to go from two rows to have all the data in one row. So basically I want to loop through the reocrds and based on the end date of the record being null and using the telecom_type_name put the data into the new table. And if there is nothing there, for example no email address, insert 'No Email Address'. Does this make better sense?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 14:03:46

So you want to take perfectly normalized data and denormalize it

WHY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spikr77
Starting Member

6 Posts

Posted - 2009-11-12 : 15:15:13

Hi Brett,
Well the reson being is I want to create a table that has a persons contact information in it that is the most current and active information. I gave a small example of the table but one person could have literally dozens of rows in the table, active and incative records. I have people that write reports and they need the most accurate data and I figued I could create a view that would give them this. Hope that helps.
Mike

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-12 : 15:29:26

Yes

I would agree with a view, not a denormalized table that needs maintenance.

Do you have rules that determine what is your "best" data.

And you must want a upper limit of the number of phones you want in this view.

We know one rule #1. They must be active rows.

So let's say you have 10 active rows, and you only want to display 3 active numbers.

What rules do you have for that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spikr77
Starting Member

6 Posts

Posted - 2009-11-16 : 08:49:21

So yes the first rule is the phone number end date must be null. Typically there will not be multiple active number types (eg 2 home number, 2 cell numbers etc) See I really don't know what can be done when looping through data for checking, hence the asking of the question. I guess there could be instances where we would get more than one active phone type, but really all I want is one active record of each type. So one phone, one cell, etc. And if there isn't an active record to just put in a null. I really hope someone here can help me on this one. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 10:19:57

Listen,

Either

1). Use a cursor
2). Lopp through the data, or
3). Use a set based solution

In ANY case, all three of them would HAVE to apply the same RULES

WHICH you haven't defined yet

That's Job #1

Once that's done, we can give you an optimal solution

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spikr77
Starting Member

6 Posts

Posted - 2009-11-16 : 10:55:32

Thanks Brett, sorry for my ignorance, still new to all this. Ok so the rules, which I hope are the rules and if not please let me know.
HomePhone = Telephone_Type_Id = Home and End_Date IS NULL
CellPhone = Telephone_Type_Id = Cell and End_Date IS NULL
EmailAddress = Telephone_Type_ID = Internet and End_Date IS NULL

That is basically what I am looking for. I am guessing I will have to do three different loops, each one to get the required data for that loop and stick it into the view.

Now you listed three different ways of doing it, can you maybe explain the pros and cons of each?

Thanks.

MuadDBA

628 Posts

Posted - 2009-11-16 : 12:54:54

I think you're misunderstanding what a view is. A view is simply a predefined query that is stored as an object in the database. You don't loop through anything to stick something into a view. Instead, the view would execute a specific select statement that would coalesce this data in the way that you want it.

I'm not sur eI have the necessary SQL skills to QUICKLY write this query for you, but it can be done, and it would leave your data nice and normalized.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 14:27:16

quote:
Originally posted by spikr77

Thanks Brett, sorry for my ignorance, still new to all this. Ok so the rules, which I hope are the rules and if not please let me know.
HomePhone = Telephone_Type_Id = Home and End_Date IS NULL
CellPhone = Telephone_Type_Id = Cell and End_Date IS NULL
EmailAddress = Telephone_Type_ID = Internet and End_Date IS NULL

That is basically what I am looking for. I am guessing I will have to do three different loops, each one to get the required data for that loop and stick it into the view.

Now you listed three different ways of doing it, can you maybe explain the pros and cons of each?

Thanks.

Is the data all in one table?

Does this get you all home phones?


SELECT [SomeID]
	,  HomePhone = [PhoneNumber]  
  FROM [Table]
 WHERE Telephone_Type_Id = 'Home' AND End_Date IS NULL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

spikr77
Starting Member

6 Posts

Posted - 2009-11-17 : 08:16:56

Yes that select statement would pull all the home phone numbers. Thanks CrazyJoe, and yes I do know what a view is, when I said stick it into the view I do realize that data can not be dumped to a view. My terminology is just very poor... :) lol.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources