Need help with SELECT statement.

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)

Need help with SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2011-01-18 : 13:10:19

[code]Hi,

Give the tables below. I need to get a fax number from StagingCust with the same CustomerContactId insert into another table and
get stuck. Please see the desired output below. Any help is greatly appreciate. I am using SQL 2008.

Again, Thanks in advance.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LUPhoneType]') AND type in (N'U'))
DROP TABLE [dbo].[LUPhoneType]
GO

CREATE TABLE [dbo].[LUPhoneType](
[PhoneId] [int] NOT NULL,
[PhoneTypeCode] [varchar](10) NOT NULL
) ON [PRIMARY]

GO

INSERT INTO [dbo].[LUPhoneType] ([PhoneId], [PhoneTypeCode])
SELECT 1, N'Work' UNION ALL
SELECT 2, N'Cell' UNION ALL
SELECT 3, N'Fax';
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerContact1]') AND type in (N'U'))
DROP TABLE [dbo].[CustomerContact1]
GO

CREATE TABLE [dbo].[CustomerContact1](
[CustomerContactId] [int] NOT NULL,
[CustomerCode] [char](3) NOT NULL,
[FirstName] [varchar](40) NOT NULL,
[LastName] [varchar](40) NOT NULL,
[Title] [varchar](30) NULL
)
GO

INSERT INTO [dbo].[CustomerContact1]([CustomerContactId], [CustomerCode], [FirstName], [LastName], [Title])
SELECT 1, N'BOA', N'Lisa', N'Smith', N'President' UNION ALL
SELECT 2, N'BOA', N'Peter', N'Lee', N'CEO' UNION ALL
SELECT 3, N'BOA', N'John', N'Nguyen', N'Partner' UNION ALL
SELECT 4, N'KON', N'Marine', N'Lorenze', N'Asset Coordinator' UNION ALL
SELECT 5, N'KON', N'Bloch', N'Melissa', N'Asset Coordinator' UNION ALL
SELECT 6, N'KON', N'Debie', N'Lee', N'Director' UNION ALL
SELECT 7, N'WLB', N'first', N'test', N'President' UNION ALL
SELECT 8, N'WLB', N'Contact First Name 2', N'Contact Last Name 2', N'CEO' UNION ALL
SELECT 9, N'WLB', N'Contact First Name 3', N'Contact Last Name 3', N'Partner' UNION ALL
SELECT 10, N'CHA', N'first2', N'test2', N'Asset Coordinator' UNION ALL
SELECT 11, N'CHA', N'Melissa', N'Bloch', N'Asset Coordinator' UNION ALL
SELECT 12, N'CHA', N' ', N' ', N'' UNION ALL
SELECT 13, N'UNB', N'first3', N'test3', N'My Title' UNION ALL
SELECT 14, N'UNB', N'Contact First Name 2', N'Contact Last Name 2', N'my title' UNION ALL
SELECT 15, N'UNB', N'Contact First Name 3', N'Contact Last Name 3', N'Partner' UNION ALL
SELECT 16, N'MLB', N'first4', N'test4', N'ml title' UNION ALL
SELECT 17, N'MLB', N'ml contactfname', N'ml contactlname', N'test title' UNION ALL
SELECT 18, N'MLB', N'first name', N'contact title', N'title'
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StagingCust]') AND type in (N'U'))
DROP TABLE [dbo].[StagingCust]
GO

CREATE TABLE [dbo].[StagingCust](
[CustomerID] [varchar](5) NULL,
[ContactLastName1] [varchar](10) NULL,
[ContactFirstName1] [varchar](10) NULL,
[ContactPhone1] [varchar](20) NULL,
[ContactFax1] [varchar](20) NULL,
[ContactEmail1] [varchar](25) NULL,
[ContactLastName2] [varchar](10) NULL,
[ContactFirstName2] [varchar](10) NULL,
[ContactPhone2] [varchar](20) NULL,
[ContactFax2] [varchar](20) NULL,
[ContactEmail2] [varchar](25) NULL,
[ContactLastName3] [varchar](10) NULL,
[ContactFirstName3] [varchar](10) NULL,
[ContactPhone3] [varchar](20) NULL,
[ContactFax3] [varchar](20) NULL,
[ContactEmail3] [varchar](25) NULL
) ON [PRIMARY]
GO

BEGIN TRANSACTION;
INSERT INTO [dbo].[StagingCust]([CustomerID], [ContactLastName1], [ContactFirstName1], [ContactPhone1], [ContactFax1], [ContactEmail1], [ContactLastName2], [ContactFirstName2], [ContactPhone2], [ContactFax2], [ContactEmail2], [ContactLastName3], [ContactFirstName3], [ContactPhone3], [ContactFax3], [ContactEmail3])
SELECT N'BOA', N'Smith ', N'Lisa', N'2131234567', N'2131234765', N'contact1@boa.com', N'Lee', N'Peter', N'2131234678', N'2131234876', N'contact2@boa.com', N'Nguyen', N'John', N'2131234789', N'2131234987', N'contact3@boa.com' UNION ALL
SELECT N'KON', N'Lorenze', N'Marine', N'714617016', N'714617610', N'lmarin@kon.com', N'Melissa', N'Bloch', N'7146402578', N'7146402875', N'mbloch@kon.com', N'Lee', N'Debie', N'7148681415', N'7146982458', N'Ldbie@Test.com' UNION ALL
SELECT N'WLB', N'test', N'first', N'7417417417', N'4014104110', N'contact1@boa.com', N'Contact La', N'Contact Fi', N'2131234678', N'2131234876', N'contact2@boa.com', N'Contact La', N'Contact Fi', N'2131234789', N'2131234987', N'contact3@boa.com' UNION ALL
SELECT N'CHA', N'test2', N'first2', N'8528528522', N'7417417417', N'lmarin@kon.com', N'Bloch', N'Melissa', N'7146402578', N'7146402875', N'mbloch@kon.com', N' ', N' ', NULL, NULL, NULL UNION ALL
SELECT N'UNB', N'test3', N'first3', N'9639639633', N'7414569632', N'MLAST@QA.COM', N'Contact La', N'Contact Fi', N'2131234678', N'2131234876', N'contact2@boa.com', N'Contact La', N'Contact Fi', N'4564564564', N'4564564564', N'contact3@boa.com' UNION ALL
SELECT N'MLB', N'test4', N'first4', N'7417417417', N'4564555456', N'mlloan@yahoo.com', N'ml contact', N'ml contact', N'7897897897', N'5675675675', N'contact2@mll.com', N'contact ti', N'first name', N'2342342342', N'1231231231', N'email@test.com'
COMMIT;
RAISERROR (N'[dbo].[StagingCust]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerContactNumber]') AND type in (N'U'))
DROP TABLE [dbo].[CustomerContactNumber]
GO

CREATE TABLE [dbo].[CustomerContactNumber](
[CustomerCode] [char](3) NOT NULL,
[CustomerContactId] [int] NOT NULL,
[PhoneTypeCode] [varchar](5) NOT NULL,
[ContactNumber] [varchar](20) NULL,
[Email] [varchar](50) NULL
CONSTRAINT [XPKCustomerContactNumber] PRIMARY KEY CLUSTERED
(
[CustomerCode] ASC,
[CustomerContactId] ASC,
[PhoneTypeCode] ASC
)
) ON [PRIMARY]

GO

SELECT *
FROM LUPhoneType;
GO

PhoneId PhoneTypeCode
----------- -------------
1 Work
2 Cell
3 Fax

SELECT *
FROM CustomerContact1;
GO

CustomerContactId CustomerCode FirstName LastName Title
----------------- ------------ ---------------------------------------- ---------------------------------------- ------------------------------
1 BOA Lisa Smith President
2 BOA Peter Lee CEO

SELECT *
FROM StagingCust;
GO

CustomerID ContactLastName1 ContactFirstName1 ContactPhone1 ContactFax1 ContactEmail1 ContactLastName2 ContactFirstName2 ContactPhone2 ContactFax2 ContactEmail2 ContactLastName3 ContactFirstName3 ContactPhone3 ContactFax3 ContactEmail3
---------- ---------------- ----------------- -------------------- -------------------- ------------------------- ---------------- ----------------- -------------------- -------------------- ------------------------- ---------------- ----------------- -------------------- -------------------- -------------------------
BOA Smith Lisa 2131234567 2131234765 contact1@boa.com Lee Peter 2131234678 2131234876 contact2@boa.com Nguyen John 2131234789 2131234987 contact3@boa.com
KON Lorenze Marine 714617016 714617610 lmarin@kon.com Melissa Bloch 7146402578 7146402875 mbloch@kon.com Lee Debie 7148681415 7146982458 Ldbie@Test.com
WLB test first 7417417417 4014104110 contact1@boa.com Contact La Contact Fi 2131234678 2131234876 contact2@boa.com Contact La Contact Fi 2131234789 2131234987 contact3@boa.com
CHA test2 first2 8528528522 7417417417 lmarin@kon.com Bloch Melissa 7146402578 7146402875 mbloch@kon.com NULL NULL NULL
UNB test3 first3 9639639633 7414569632 MLAST@QA.COM Contact La Contact Fi 2131234678 2131234876 contact2@boa.com Contact La Contact Fi 4564564564 4564564564 contact3@boa.com
MLB test4 first4 7417417417 4564555456 mlloan@yahoo.com ml contact ml contact 7897897897 5675675675 contact2@mll.com contact ti first name 2342342342 1231231231 email@test.com

-- Get the fax number form StagingCust table and insert into CustomerContactNumber with the same CustomerContactId.
-- Please see desired output below.

--Testing...

-- INSERT CustomerContactNumber ( CustomerCode, CustomerContactId, NumberTypeCode, Phone, Fax, Email )
SELECT cc.CustomerCode,
cc.CustomerContactId,
--'Cell' AS 'Cell',
CASE
WHEN LEN(c.Email) > 0 THEN 'Cell'
ELSE 'Fax'
END AS 'PhoneTypeCode',
c.Phone,
--c.Fax,
CAST(c.Email AS VARCHAR(35)) AS 'Email'
--,cc.rn AS 'cc.rn'
--,c.rn AS 'c.rn'
--,c.Phone AS 'c.Phone'
--,c.Fax AS 'c.Fax'
--,c.Email AS 'c.Email'
FROM StagingCust AS s
CROSS APPLY ( SELECT 1, ContactPhone1, ContactEmail1
UNION ALL
SELECT 1, ContactFax1, ''
UNION ALL
SELECT 2, ContactPhone2,ContactEmail2
UNION ALL
SELECT 2, ContactFax2, ''
UNION ALL
SELECT 3, ContactPhone3, ContactEmail3
UNION ALL
SELECT 3, ContactFax3, ''
) AS c ( rn, Phone, Email)
INNER JOIN ( SELECT CustomerCode,
CustomerContactId,
ROW_NUMBER() OVER ( PARTITION BY CustomerCode ORDER BY CustomerContactId ASC) AS rn
FROM CustomerContact1 ) AS cc
ON cc.CustomerCode = s.CustomerID
AND cc.rn = c.rn
WHERE NOT ( c.Phone IS NULL
--AND c.Fax IS NULL
AND c.Email IS NULL);
GO

-- Desired output:
CustomerCode CustomerContactId Cell Phone Email
------------ ----------------- ---- -------------------- -------------------
BOA 1 Cell 2131234567 contact1@boa.com
BOA 1 Fax 2131234765

BOA 2 Cell 2131234678 contact2@boa.com
BOA 2 Fax 2131234876

BOA 3 Cell 2131234789 contact3@boa.com
BOA 3 Fax 2131234987 [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources