April 23, 2013 at 5:36 am
/****** Object: Table [dbo].[PhoneTable] Script Date: 4/23/2013 4:55:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PhoneTable](
[PhoneNumber] [varchar](10) NULL,
[PhoneType] [nvarchar](4) NULL,
[NameID] [int] NOT NULL,
[AssocID] [int] NOT NULL,
[SortOrder] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2626770315', N'HOME', 1000, 20, 0)
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2626270104', N'CELL', 1000, 20, 1)
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'0003343553', N'HOME', 1000, 10, 0)
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'0003354427', N'WORK', 1000, 10, 2)
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'2628479870', N'CELL', 1000, 30, 1)
GO
INSERT [dbo].[PhoneTable] ([PhoneNumber], [PhoneType], [NameID], [AssocID], [SortOrder]) VALUES (N'3458790', N'WORK', 1000, 30, 2)
GO
/****** Object: Table [dbo].[NamePhone] Script Date: 4/23/2013 4:56:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NamePhone](
[PhoneNbr1] [varchar](20) NULL,
[PhoneType1] [char](15) NULL,
[PhoneNbr2] [varchar](20) NULL,
[PhoneType2] [char](1) NULL,
[NameID] [int] NOT NULL,
[AssocID] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 20)
GO
INSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 10)
GO
INSERT [dbo].[NamePhone] ([PhoneNbr1], [PhoneType1], [PhoneNbr2], [PhoneType2], [NameID], [AssocID]) VALUES (NULL, NULL, NULL, NULL, 1000, 30)
GO
Hi Friends,
I want to update Phone number 1 and phone number 2 in NamePhone table from Phone Table....
Requirements:
1. If a AssocID has a Home number , it has to update to phone number1 else cell number else work number.......as you can see the sortorder field in PhoneTable....
2. If Phone number 1 field is having home number then update phone number 2 field with cell number else work number....if phone number 1 has cell number then update phone number 2 with work number....
I tried to do this with case statement, but some how I failed....So i need your suggesstions friends.....
April 23, 2013 at 8:03 am
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 8:15 am
Sean Lange (4/23/2013)
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.
Sean, I just gave the same data with temporary table.....
If you are looking for some thing else , please let me know....i will give you the complete details.....and Are you clear with my requirements, Sean? If no, please let me know that also...
April 23, 2013 at 8:35 am
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.Sean, I just gave the same data with temporary table.....
If you are looking for some thing else , please let me know....i will give you the complete details.....and Are you clear with my requirements, Sean? If no, please let me know that also...
Can you post what the desired output should be?
I still don't get why you want to have a denormalized table like that but I guess that is up to you. The update is fairly simple once I know what all the rules are.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 9:03 am
Sean Lange (4/23/2013)
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.Sean, I just gave the same data with temporary table.....
If you are looking for some thing else , please let me know....i will give you the complete details.....and Are you clear with my requirements, Sean? If no, please let me know that also...
Can you post what the desired output should be?
I still don't get why you want to have a denormalized table like that but I guess that is up to you. The update is fairly simple once I know what all the rules are.
Sean, Here i have attached the image file with expected output....Please take a look at it...
FYI.....Home Phone number is top priority then cell then work and so on...so need to update the field based on the priorities....i mean if ID has a home phone number, it must go to phone number1 field , rest goes to phone number2 field.....if a ID does not have home phone number , then we have to look for the next priority ...
April 23, 2013 at 9:25 am
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.Sean, I just gave the same data with temporary table.....
If you are looking for some thing else , please let me know....i will give you the complete details.....and Are you clear with my requirements, Sean? If no, please let me know that also...
Can you post what the desired output should be?
I still don't get why you want to have a denormalized table like that but I guess that is up to you. The update is fairly simple once I know what all the rules are.
Sean, Here i have attached the image file with expected output....Please take a look at it...
FYI.....Home Phone number is top priority then cell then work and so on...so need to update the field based on the priorities....i mean if ID has a home phone number, it must go to phone number1 field , rest goes to phone number2 field.....if a ID does not have home phone number , then we have to look for the next priority ...
Thanks that cleared up what you are trying to do. UGH!!! I would question however has asked you to create this denormalized table but the following query will do what you are asking on the sample data provided.
update NamePhone
set PhoneNbr1 = coalesce(home.PhoneNumber, cell.PhoneNumber, work.PhoneNumber),
PhoneType1 = case when home.PhoneNumber IS not null then 'H' when cell.PhoneNumber is not null then 'C' else 'W' end,
PhoneNbr2 = case when home.PhoneNumber IS not null then coalesce(cell.PhoneNumber, work.PhoneNumber) else work.PhoneNumber end,
PhoneType2 = case when home.PhoneNumber IS not null and cell.PhoneNumber is not null then 'C' else 'W' end
from NamePhone n
left join PhoneTable home on home.AssocID = n.AssocID and home.PhoneType = 'Home'
left join PhoneTable cell on cell.AssocID = n.AssocID and cell.PhoneType = 'cell'
left join PhoneTable work on work.AssocID = n.AssocID and work.PhoneType = 'work'
The real problem with type of thing is that it is totally unflexible. If you want/need to add a new number you have to modify the table instead of just adding a new row. 😉
--EDIT--
Mixed up the quotes when posting.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2013 at 10:45 am
Sean Lange (4/23/2013)
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
prakashr.r7 (4/23/2013)
Sean Lange (4/23/2013)
What is the point of NamePhone? Your table PhoneName is properly normalized and the second table is not. I would drop NamePhone and use PhoneName.Sean, I just gave the same data with temporary table.....
If you are looking for some thing else , please let me know....i will give you the complete details.....and Are you clear with my requirements, Sean? If no, please let me know that also...
Can you post what the desired output should be?
I still don't get why you want to have a denormalized table like that but I guess that is up to you. The update is fairly simple once I know what all the rules are.
Sean, Here i have attached the image file with expected output....Please take a look at it...
FYI.....Home Phone number is top priority then cell then work and so on...so need to update the field based on the priorities....i mean if ID has a home phone number, it must go to phone number1 field , rest goes to phone number2 field.....if a ID does not have home phone number , then we have to look for the next priority ...
Thanks that cleared up what you are trying to do. UGH!!! I would question however has asked you to create this denormalized table but the following query will do what you are asking on the sample data provided.
update NamePhone
set PhoneNbr1 = coalesce(home.PhoneNumber, cell.PhoneNumber, work.PhoneNumber),
PhoneType1 = case when home.PhoneNumber IS not null then 'H' when cell.PhoneNumber is not null then 'C' else 'W' end,
PhoneNbr2 = case when home.PhoneNumber IS not null then coalesce(cell.PhoneNumber, work.PhoneNumber) else work.PhoneNumber end,
PhoneType2 = case when home.PhoneNumber IS not null and cell.PhoneNumber is not null then 'C' else 'W' end
from NamePhone n
left join PhoneTable home on home.AssocID = n.AssocID and home.PhoneType = 'Home'
left join PhoneTable cell on cell.AssocID = n.AssocID and cell.PhoneType = 'cell'
left join PhoneTable work on work.AssocID = n.AssocID and work.PhoneType = 'work'
The real problem with type of thing is that it is totally unflexible. If you want/need to add a new number you have to modify the table instead of just adding a new row. 😉
--EDIT--
Mixed up the quotes when posting.
Thank you, Sean.....had to do some slight changes....and it worked.....Thank you again...
April 23, 2013 at 10:54 am
prakashr.r7 (4/23/2013)
Thank you, Sean.....had to do some slight changes....and it worked.....Thank you again...
You're welcome. Glad I was able to get you close enough to figure out the last couple steps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply