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
 General SQL Server Forums
 New to SQL Server Programming
 Producing Duplicate Records

Author  Topic 

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-19 : 04:39:29

Hey guys

Hope your well,
I am a little stuck with the query that i have built and i don’t understand why i am producing duplicates

In table [FDMS].[SalesForce].[DailyAccounts] every account_id is unique

However when i do my Final Build with Fdmsaccount ParentID i am producing multiple duplicate account ids.


does anyone have any ideas why ?
This is my query


-- Selecting Internal & External Where confirmation = 'y'---

truncate table #502



CREATE UNIQUE NONCLUSTERED INDEX [pk] ON #502
(
[FDMSAccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


insert into #502
([FDMSAccountNo]
,[External_ID])


--- need to run this first before any account build ---
-------------------------------------------------------
select
[FDMSAccountNo],
[External_ID]
--into #502
from [FDMS].[dbo].[stg_LMPAB502]
where Client_Conf ='y'
-------------------------------------------------------
-------------------------------------------------------


--- Insert into xref Link ---
insert into #502
([FDMSAccountNo]
,[External_ID])

--- Selecting x ref table--
Select
[FDMSAccountNo]
,left([ExternalAccountNo],15)
from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]
where [Correspondence_Flag] ='y'


--External Mid join --
SELECT
[Account_ID],
[FDMSAccountNo]
Into #Update
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_External],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[External_ID],18)

insert into #Update
([Account_ID],
[FDMSAccountNo])
--Internal Mid join --
SELECT
[Account_ID],
[FDMSAccountNo]
FROM [FDMS].[SalesForce].[DailyAccounts]
inner join [FDMS].[dbo].[stg_LMPAB502] on
RIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18)


Select distinct
Account_id,
#Update.FDMSAccountNo,
External_ID
into #sf
from #Update
inner join #502 on #502.FDMSAccountNo = #Update.FDMSAccountNo


--- linking Address to correct Sf account id ---
SELECT [FDMSAccountNo]
,Parentsfid
,[MM3-DBA-NAME] as [DBA Name]
,[LEGAL-NAME] as [Legal Name]
,[MM3-DBA-ADDR1] as 'Street'
,[MM3-DBA-ADDR2]
,[MM3-DBA-ADDR4]
,[MM3-DBA-CITY] as 'City'
,[MM3-DBA-COUNTY] as 'County'
,[MM3-DBA-POSTAL-ZIP] as 'Postalcode'
,[MM3-DBA-COUNTRY]as 'Country'
,[MM3-DBA-TELEPHONE]as 'Phone'
,left (CONVERT(varchar(20),DATEADD(minute,DATEDIFF(minute,0,'23:01'),CONVERT(datetime,[INSTALLATION_DATE],112)),126),16)as Open_Date,
CASE WHEN LEFT (CANCELLATION_DATE , 4) >= '1970' AND LEFT (CANCELLATION_DATE , 4) < '9999' AND CANCELLATION_DATE <> '20' AND CANCELLATION_DATE <> '20000000' THEN CONVERT(varchar(20),convert(date,CANCELLATION_DATE,109))+'T23:01' end as Cancel_Date,
CASE WHEN LEFT (last_posting_date , 4) >= '1980' AND LEFT (last_posting_date , 4) < '9999' AND last_posting_date <> '20' AND last_posting_date <> '20000000' THEN CONVERT(varchar(20),convert(date,last_posting_date,109))+'T23:01' end as Last_Post_Date,
CASE WHEN LEFT (First_Post_Date , 4) >= '1980' AND LEFT (First_Post_Date , 4) < '9999' AND First_Post_Date <> '20' AND First_Post_Date <> '20000000' THEN CONVERT(varchar(20),convert(date,First_Post_Date,109))+'T23:01' end as First_Post_Date,
Case when [FDMSAccountNo]IS NOT NULL THEN '00530000007Yscj' ELSE '00530000007Yscj' END [BoS Owner],
Case when [FDMSAccountNo]IS NOT NULL THEN '01230000000LUxt' ELSE '01230000000LUxt' END [RecordTypeId]
Into #Address
FROM [FDMS].[dbo].[stg_LMPAB501]


--- Building ParentSf Hierarchy --

inner join (
---Outlet to chain --
select distinct substring([MM-CHN-CORP],2,12) as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and FDMSAccountNo <> substring([MM-CHN-CORP],2,12)


-- Outlet to Corp--
union all
select distinct substring([MM-CHN-CORP],2,12) as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and substring([MM-CHN-CORP],2,12) = '000000000000'
and FDMSAccountNo <>substring([MM-CHN-CORP],2,12)


-- Outlet to Agent --
union all
select distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) = '000000000000'
and substring([MM-CHN-CORP],2,12) = '000000000000'
and FDMSAccountNo <> substring([MM-CHN-AGENT],2,12)

-- Chain to Corp--
union all
select distinct substring([MM-CHN-CORP],2,12) as Parentsfid
,substring([MM-CHN-CORP],2,12) as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and substring([MM-CHN-CORP],2,12) <> '000000000000'

-- Chain to agent--
union all
select distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,substring([MM-CHN-CORP],2,12) as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and substring([MM-CHN-CORP],2,12) = '000000000000'

-- Corp to agent --
union all
select Distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,substring([MM-CHN-CORP],2,12) as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000')
x
on x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]


group by
[FDMSAccountNo],
Parentsfid,
[MM3-DBA-NAME] ,
[LEGAL-NAME],
[MM3-DBA-ADDR1],
[MM3-DBA-ADDR2],
[MM3-DBA-ADDR4],
[MM3-DBA-CITY] ,
[MM3-DBA-COUNTY] ,
[MM3-DBA-POSTAL-ZIP] ,
[MM3-DBA-COUNTRY],
[MM3-DBA-TELEPHONE],
[INSTALLATION_DATE],
[CANCELLATION_DATE],
LAST_POSTING_DATE,
FIRST_POST_DATE



--Final Build with Fdmsaccount ParentID--
Select distinct
#Address.FDMSAccountNo,
#Address.[DBA Name],
#Address.[Legal Name],
#Address.Street,
#Address.[MM3-DBA-ADDR2],
#Address.[MM3-DBA-ADDR4],
#Address.City,
#Address.County,
#Address.Postalcode,
#Address.Country,
#Address.Phone,
#Address.Open_Date,
#Address.Cancel_Date,
#Address.Last_Post_Date,
#Address.[BoS Owner],
#Address.RecordTypeId,
u.FDMSAccountNo,
#Address.Parentsfid,
u.account_id,
u1.account_id as parentid
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno
order by Open_Date desc

--------------------------------------

--Finding Duplicates Mids --
SELECT
u.FDMSAccountNo, COUNT(*)
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 ON #Address.Parentsfid = u1.fdmsaccountno
GROUP BY u.FDMSAccountNo
HAVING COUNT(*) > 1

--Finding Duplicates ACCOUNT IDS --

SELECT
u.FDMSAccountNo,
U.ACCOUNT_ID
from #Address
inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo
left join #Update u1 ON #Address.Parentsfid = u1.fdmsaccountno
WHERE U.FDMSAccountNo = '878592020886'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 04:42:03
difficult to say from query without seeing any data
My guess is tables are related by one to many relationshio which might be causing this.

Post some sample data from tables and show what output you want from them. Then we may be able to suggest an alternate approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-19 : 06:36:37
hi Visakh16

I re looked over the code i and i found one obvious mistake (School boy error)

--- Selecting x ref table--
Select
[FDMSAccountNo]
,left([ExternalAccountNo],15)
from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]
where [Correspondence_Flag] ='y'

it should be

Select
[FDMSAccountNo]
,right([ExternalAccountNo],15)
from [FDMS].[dbo].[stg_FDMS_Link_New_Xref]
where [Correspondence_Flag] ='y'

that has removed alot of duplicate in the intial build :)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:47:50
cool
glad that you got it sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-06-19 : 07:07:27
Thanks for the hint, it really helped
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 10:22:38
no problem you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -