SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Producing Duplicate Records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/19/2013 :  04:39:29  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 06/19/2013 :  04:42:03  Show Profile  Reply with Quote
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 - 06/19/2013 :  06:36:37  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 06/19/2013 :  06:47:50  Show Profile  Reply with Quote
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 - 06/19/2013 :  07:07:27  Show Profile  Reply with Quote
Thanks for the hint, it really helped
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  10:22:38  Show Profile  Reply with Quote
no problem you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000