Confused with mapping a table - 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 2008 Forums

Transact-SQL (2008)

Confused with mapping a table

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 06:45:51

Hi, I am trying to pair phone numbers together based on a category mapping table. The need to be paired for different business markets (T5 and TW). (This is a made up example and doesnt necessarily make sense with the matches

)

phone number category Table :

CategoryID-----CategoryName

0-----------NULL
1-----------Very Common
2-----------Common
3-----------100%
4-----------95-99%
5-----------85-94%
6-----------75-84%
7-----------50-74%
8-----------<=50%
9-----------Rare
10----------Very Rare
11----------Unique
12----------No Match
13----------Other

Business category mapping Table

CategoryMappingID---T5MapID---TWMapID

1----------------1---------11
2----------------2---------2
3----------------3---------3
4----------------4---------4
5----------------5---------5
6----------------6---------6
7----------------7---------7
8----------------8---------12
9----------------0---------9
10---------------4---------10

Phone numbers table : (Category is stored as a string unfortunately)

PhoneNumberID----PhoneNumber----category

1----------------555123456------Common
2----------------555123456------Rare
3----------------555123456------50-74%
4----------------555123456------Common
5----------------555123456------Common

These phonenumbers need to be displayed across 2 business categories (T5 and TW) in a table like

BusinessMatchID------T5PhoneNumber-----TWPhoneNumber
1--------------------555123456---------555123456
2--------------------555123456---------555123434
3--------------------555123457---------555123489
4--------------------555123455---------555123434
5--------------------555123454---------555123467

I'm confused how i can use the mapping table when I do the table joins to make sure the right numbers are mapped to each other.

I hope I am being clear. Kinda hard to explain

Any advice is great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 06:52:39

i didnt understand how you will get sample output shown. can you explain the logic?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 07:02:44

ok, ill try to explain.

Basically there's a huge list of phone numbers in a table. Each phone number has a category (common, rare etc.)

Now, there is a second business category which applies different categories to the existing phone numbers.

So, I thought it would make sense to map old categories with new categories so the new category could be determined. (i.e. if it's 'common' for existing number then it's 'unique' for new business)

For the first business category mapping :

CategoryMappingID---T5MapID---TWMapID
1-------------------1---------11

All existing phone numbers that have 'Very Common' Category for T5 business should be mapped to 'Unique' category for TW business.

Does this clear things up ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:19:22

nope...not quite. how did you get numbers against each phone number

for example,

2--------------------555123456---------555123434

i cant even see 555123434 inside Phone numbers table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 07:32:22

OK, sorry. I'm having difficulty explaining it myself.

Maybe this is easier. I want to loop through each cell below and call a stored procedure to get a value and insert into a temp table

T5MapID TWMapID T9MapID
1-------11------13
2-------2-------2
3-------3-------3
4-------4-------4
5-------5-------5
6-------6-------6
7-------7-------7
8-------12------8
0-------9-------0
4-------10------0

So, there would be 3 calls per row i.e. 1st row (1,11,13)
Then it would move onto the second row etc. etc.

The insert would be into a temp table with exactly the same structure but with different column names.

Could you tell me how i can call the stored procedure in the sequence i require ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 07:41:19

are these values 1,11,13 etc used to look up against category table? if yes how would you transalate these to phone numbers?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 08:20:39

Yes, they will be used to lookup a category table.

Passing this ID along with another variable into a stored procedure will return the phone number i need.

I just need to understand the sequence for calling the sproc 3 times for each row

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:01:34

quote:
Originally posted by grimmus

Yes, they will be used to lookup a category table.

Passing this ID along with another variable into a stored procedure will return the phone number i need.

I just need to understand the sequence for calling the sproc 3 times for each row

Thank you

you cant call procedure inline for each row of resultset. for that you need to use some cursor or looping logic? why not make it a user defined function in which case you can call it inline and achieve your requirement using a set based solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 09:04:28

Thanks for the reply.

Yes, i could use a user defined function instead. But i dont understand what you mean by 'set based function'

Could you provide a simple example of how i would make the function calls please ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 09:13:33

quote:
Originally posted by grimmus

Thanks for the reply.

Yes, i could use a user defined function instead. But i dont understand what you mean by 'set based function'

Could you provide a simple example of how i would make the function calls please ?

set based means you call it inline in select query so that it gets executed as a batch
in cursor or looping logic, you iterate row by row and do some processing over each row in each iteration which will obviously be much slower than your set based logic on almost all occasions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 09:43:46

OK, thanks. I get the set based idea now.

I'm still unsure how to do the insert based on the table below. Because i need these row values to do the insert to the new table

T5MapID TWMapID T9MapID
1-------11------13
2-------2-------2
3-------3-------3
4-------4-------4
5-------5-------5
6-------6-------6
7-------7-------7
8-------12------8
0-------9-------0
4-------10------0

The user defined function would take the values in from this table and then return the result which would be stored in the new table.

So, the function could be called convertCatIDToPhoneNumber. And the params would be CatID and another value that together can determine the phone number

But, how can i call this function with the different CatID for each cell in the table ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:06:29

it would be as simple as


SELECT dbo.convertCatIDToPhoneNumber(T5MapID,<another value here>) AS FirstNumber,
dbo.convertCatIDToPhoneNumber(TWMapID,<another value here>) AS SecondNumber,
dbo.convertCatIDToPhoneNumber(T9MapID,<another value here>) AS ThirdNumber
FROM Table
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-15 : 10:12:08

Thank you Visakh ! You have saved my life once again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 10:34:06

no probs

yu're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources