Duplicate Records in SQL Select

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)

Duplicate Records in SQL Select

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-07-30 : 09:54:02

Good morning, I have a complex select statement that is retreiving records from a sql crm database. The problem is that I am getting multiple records back because the member may have several contact entries, with different areas of the table filled in correctly and others not filled in.

Lets see if I can explain a little better:

Fields:

Account Email Industry ContactIndustry Mailings

1 paul.woodworth@suntrust.com Affordable Housing NULL TaxCredit
1 paul.woodworth@suntrust.com NULL Affordable Housing NULL
2 paul.woodworth@suntrust.com Affordable Housing NULL Affordable Housing
2 paul.woodworth@suntrust.com NULL NULL Affordable Housing

So if I do a select something like this I get 4 rcords

select email , industry, contact,industry,mailings from x table
inner join y table on y.accountid = x.accountid

where y.industry like '%Affordable%
or
x.contactindustry like '%Affordable%
or
x.mailings like '%Affordable%

What I need to return is one record that would combine the above 4 records into one.
In other words the record would need to be create with the combination of the "good" selects.

Record Email Industry ContactIndustry Mailings

1 paul.woodworth@suntrust.com Affordable Housing Affordable Housing Affordable Housing

Sorry for the long explanation, I hope this is possible. I'm stumped.

Bryan Holmstrom

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-30 : 12:39:27

Create a temporary table then pull the unique email then populate the remaining

djj

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-07-30 : 13:01:00

I am using a temp table, how do I select the unique emails, and then populate the table with the remaing fields with the correct data?

Here is the entire script...kind of long

DELETE FROM [Lyris_Temp].[dbo].[Lyris_Combined]
WHERE [Lyris_Combined].[Con_SlxSingleMailing] = 'AffHousing'
AND
[Lyris_Combined].[Comment_] = 'Dynamics Record'
GO

IF OBJECT_ID('TmpTable') IS NOT NULL DROP TABLE TmpTable

/* Create Temporary Table Hold the data */
SET NOCOUNT ON;
CREATE TABLE TmpTable
(
EmailAddr_ varchar(200),
Comment_ varchar(50),
UserId_ varchar(100),
Fullname_ varchar(100),
ReadsHtml_ varchar (1),
UserNameLC_ varchar(100),
Con_First_Name varchar(250),
Con_Last_Name varchar(250),
Con_Title varchar(250),
Con_Addr_city varchar(250),
Con_Addr_State varchar(250),
Con_Status varchar(250),
Act_Account_Name varchar(250),
Act_Type varchar(200),
Act_Relationship varchar(100),
Act_State varchar(250),
Act_Industry varchar(250),
Act_GroupNum varchar(100),
Act_Account_Manager varchar (50),
Act_City varchar (250),
Act_Subsegment varchar(100),
Act_IndustryPractice varchar(100),
Con_SlxSingleMailing varchar(20),
TmpContactID varchar(100),
Con_Mailings varchar(MAX)
)

insert into TmpTable
select DISTINCT left(lower(Contact.EmailAddress1),200) as EmailAddr_,

'Dynamics Record' as Comment_,
Contact.CONTACTID as UserID_,
left(ltrim(rtrim(contact.FirstName))+' '+ltrim(rtrim(Contact.LastName)),100) as FullName,
'T' as ReadsHtml_,
Substring(Contact.EmailAddress1,0,charindex( '@', Contact.EmailAddress1,0)) as UserNameLC_, -- sysdba.contact
left(ltrim(rtrim(Contact.FIRSTNAME)),250) as Con_First_Name,
left(ltrim(rtrim(Contact.LASTNAME)),250)as Con_Last_Name,
left(contact.Jobtitle,250) as Con_Title,
left(ContactAddress.CITY,250) as Con_Addr_City,
left(ContactAddress.stateorprovince,250) as Con_Addr_State,
Convert(VarChar(250),ContactExt.CPDC_ContactStatus, ISNULL(ContactExt.CPDC_ContactStatus,0)) as Con_Status,
left(Account.Name,250) as Act_Account_Name,
CompanyType.Value as Act_Type,

-- CRM Fields
CASE
WHEN AccountExt.cpdc_type_client = 1 Then 'Client'
WHEN AccountExt.cpdc_type_prospect = 1 Then 'Prospect'
WHEN AccountExt.cpdc_coi = 1 Then 'COI'
WHEN AccountExt.rg_vendor = 1 Then 'Vendor'
Else 'Other'
End as 'Act_Relationship',

left(CustomerAddress.stateorprovince,250) as Act_State,

IndustryName.Value AS Act_Industry,
left(account.accountnumber,250) as Act_GroupNum,
SystemUser.FullName as Act_Account_Manager,
CustomerAddress.city as Act_City,
IndustrySegName.Value as Act_Subsegment,

-- This is a repeat because Dynamics has 2 industry areas

-- Contact Practice Area and Possibly Company Practice Area
CASE
WHEN CPB.Rg_Name IS NOT NULL and pb.rg_name IS NULL then left(CPB.Rg_Name,100)
WHEN CPB.Rg_Name IS NOT NULL and pb.rg_name IS NOT NULL and pb.rg_name <> CPB.Rg_Name then left(CPB.Rg_Name+'; '+PB.RG_NAME,100)
WHEN CPB.Rg_Name IS NULL and pb.rg_name IS NOT NULL then left(PB.RG_NAME,100)
ELSE left(CPB.Rg_Name,100)
END as Act_IndustryPractice,

-- CPB.Rg_Name AS Act_IndustryPractice,
'AffHousing' as Con_SlxSingleMailing,
Contact.ContactId,
'Mailings' as Con_Mailings -- This gets filled in later

---- Temp Fields

--,Account.IndustryCode
--,IndustryName.AttributeValue
--,IndustryName.AttributeName
--,pb.rg_name

FROM [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[ContactBase] as Contact
LEFT JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[ContactExtensionBase] as ContactExt on ContactExt.Contactid = Contact.Contactid
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[CustomerAddressBase] as ContactAddress ON ContactAddress.ParentID = Contact.ContactID
LEFT JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[AccountBase] as Account ON Account.AccountID = Contact.AccountId
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[CustomerAddressBase] as CustomerAddress ON CustomerAddress.ParentID = Account.AccountID
LEFT JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[AccountExtensionBase] as AccountExt on AccountExt.AccountId = Account.AccountId
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[SystemUserBase] as SystemUser ON SystemUser.SystemUserID = Account.OwningUser
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[StringMap] as IndustryName on
Account.IndustryCode = IndustryName.AttributeValue and IndustryName.AttributeName='industrycode'

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[StringMap] as IndustrySegName on
AccountExt.cpdc_subsegment = IndustrySegName.AttributeValue and IndustrySegName.AttributeName='cpdc_subsegment'

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[StringMap] as ContactStatus on
Contact.StatusCode = ContactStatus.AttributeValue and ContactStatus.AttributeName='statuscode'

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[StringMap] as RemoveFromName on
AccountExt.cpdc_removedfrom = RemoveFromName.AttributeValue and RemoveFromName.AttributeName='cpdc_removedfrom'

LEFT JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[StringMap] as CompanyType on
AccountExt.Rg_Classification = CompanyType.AttributeValue and CompanyType.AttributeName='rg_classification'

-- Get Company Practice Areas
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[Rg_companypracticeareaExtensionBase] AS CPED
on CPED.Rg_CompanyId = Account.AccountId

-- Get Company Practice Area Name
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].Rg_practiceareaExtensionBase AS PB
on CPED.Rg_practiceareaId = PB.Rg_practiceareaId

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].Rg_practiceareaBase PAB
on PAB.Rg_practiceareaId = PB.Rg_practiceareaId

-- Get Contact Practice Areas
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[Rg_contactpracticeareaExtensionBase] AS ContPED
on ContPED.Rg_ContactId = Contact.ContactId

-- Get Contact Practice Area Name
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].Rg_practiceareaExtensionBase AS CPB
on ContPED.Rg_practiceareaId = CPB.Rg_practiceareaId

--Relate back to rg_practiceare for check of delete state code

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].Rg_practiceareaBase PCB
on PCB.Rg_practiceareaId = CPB.Rg_practiceareaId

-- Get Marketing Lists
LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[ListMemberBase] LMB
ON Contact.ContactId = LMB.EntityId

LEFT OUTER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].ListBase LB
ON LMB.ListId = LB.ListId

---- New Join to check and make sure the email is not flagged internally to Lyris and not the CRM
-- LEFT OUTER JOIN [Lyris_Prod].[dbo].[members_] LM
-- ON LM.EmailAddr_ collate SQL_Latin1_General_CP1_CI_AS = Contact.EmailAddress1

WHERE

-- (
-- Contact.EmailAddress1 = 'bhiggins@archcitydevelopment.com'
-- or
-- Contact.EmailAddress1 = 'dgasson@bostoncapital.com'
-- or
-- Contact.EmailAddress1 = 'dsmith@benjamindevco.com'
-- or
-- Contact.EmailAddress1 = 'paul.woodworth@suntrust.com'
-- )
--and

-- Real Filters make sure they are always in place for real runs
-- New check for members that are unsubscribed in Lyris but not marked in either CRM 5/5/2014 BGH
--lm.MemberType_ <> 'unsub'
--and

(PAB.DeletionStateCode IS NULL
OR (
PAB.DeletionStateCode <> 2
)
)
AND

(PCB.DeletionStateCode IS NULL
OR (
PCB.DeletionStateCode <> 2
)
)
AND

(Account.DeletionStateCode IS NULL
OR (
Account.DeletionStateCode <> 2
)
)
and

Contact.StatusCode = 1
and
Contact.EmailAddress1 LIKE '%@%'
AND
Contact.EmailAddress1 LIKE '%.%'
and
Contact.EmailAddress1 IS NOT NULL
AND
LEN(LTRIM(RTRIM(Contact.EmailAddress1))) > 4
AND
UPPER(Contact.EmailAddress1) NOT LIKE '%@RFS.COM'
AND
UPPER(Contact.EmailAddress1) NOT LIKE '%JHCOHN%'
AND
UPPER(Contact.EmailAddress1) NOT LIKE '%REZNICKGROUP%'
AND

Contact.DoNotBulkEMail <> 1
AND
Contact.DoNotSendMM <> 1
and
(ContactExt.Cpdc_Competitor IS NULL
OR (
ContactExt.Cpdc_Competitor <> 1
)
)

and
(AccountExt.rg_competitor IS NULL
OR (
AccountExt.rg_competitor <> 1
)
)
and
(AccountExt.rg_competitor IS NULL
OR (
AccountExt.rg_competitor <> 1
)
)
and
(AccountExt.cpdc_type_adverseparty IS NULL
OR (
AccountExt.cpdc_type_adverseparty <> 1
)
)
AND
(RemoveFromName.Value IS NULL
OR (
RemoveFromName.Value <> 'All Activities'
)
)
AND
(RemoveFromName.Value IS NULL
OR (
RemoveFromName.Value <> 'Communications'
)
)

AND

(
(IndustryName.Value IS NOT NULL and LTRIM(RTRIM(IndustryName.Value)) = 'Real Estate - Affordable Housing')
OR
(PB.Rg_Name IS NOT NULL AND LTRIM(RTRIM(PB.Rg_Name)) = 'Affordable Housing') -- From company practice area
OR
(CPB.Rg_Name IS NOT NULL AND LTRIM(RTRIM(CPB.Rg_Name)) = 'Affordable Housing') -- From contact practice area
OR
(LB.Listname IS NOT NULL AND LTRIM(RTRIM(LB.Listname)) = 'Affordable Housing (and LIHTC) Alerts and Events Notifications') -- From Marketing List
OR
(LB.Listname IS NOT NULL AND LTRIM(RTRIM(LB.ListName)) = 'Affordable Housing News and Views Newsletter') -- From Marketing List

)

DELETE TmpTable FROM TmpTable WHERE
EmailAddr_ IS NULL
or
EmailAddr_ NOT LIKE '%@%'
OR
EmailAddr_ NOT LIKE '%.%'
OR
LEN(LTRIM(RTRIM(EmailAddr_))) < 4
GO

-- Fill in the combined marketing lists
UPDATE a
SET Con_Mailings = STUFF((SELECT '; ' + c.ListName
FROM [Hostcrm1].[ReznickGroup_MSCRM].[dbo].ListBase c
INNER JOIN [Hostcrm1].[ReznickGroup_MSCRM].[dbo].[ListMemberBase] b
ON b.ListID = c.ListID
WHERE b.EntityID = a.TmpContactID
ORDER BY c.ListID
FOR XML PATH('')),1,1,'')
FROM TmpTable a
GO

DELETE TmpTable FROM TmpTable WHERE
Act_Industry <> 'Real Estate - Affordable Housing'
and
Act_IndustryPractice NOT LIKE '%Affordable%'
and
Con_Mailings NOT LIKE '%Affordable%'
GO

UPDATE TmpTable
SET EmailAddr_ = REPLACE(REPLACE(REPLACE(EmailAddr_,CHAR(10),''),CHAR(13),'') ,CHAR(9),'')
FROM TmpTable

GO
UPDATE TmpTable
--SET Con_Mailings = REPLACE(Con_Mailings,CHAR(10)+CHAR(13), '' )
SET Con_Mailings = REPLACE(REPLACE(REPLACE(Con_Mailings,CHAR(10),''),CHAR(13),'') ,CHAR(9),'')
FROM TmpTable
GO
UPDATE TmpTable
SET Con_Title = REPLACE(REPLACE(REPLACE(Con_Title,CHAR(10),''),CHAR(13),'') ,CHAR(9),'')
FROM TmpTable
GO

--select EmailAddr_
-- ,FullName_
-- ,Act_Industry
-- ,Act_IndustryPractice
-- ,con_Mailings
-- from TmpTable

--where EmailAddr_ = 'bhiggins@archcitydevelopment.com'
--or
--emailaddr_ = 'dgasson@bostoncapital.com'
--or
--EmailAddr_ = 'dsmith@benjamindevco.com'
--or
--emailaddr_ = 'paul.woodworth@suntrust.com'

INSERT INTO [Lyris_Temp].[dbo].[Lyris_Combined]
SELECT * FROM TmpTable

Bryan Holmstrom

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 13:06:04

Question: Could you paste your complete query? I am not sure if you are just looking for a distinct value or if you are trying to pivot data. having all the columns/ SQL output might help

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-07-30 : 13:07:11

apoligies-- posted before refreshing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources