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.
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 uniqueHowever 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] onRIGHT('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] onRIGHT('0000000000000000' + [FDMS].[SalesForce].[DailyAccounts].[MID_Internal],18) = right('0000000000000000' + [FDMS].[dbo].[stg_LMPAB502].[FDMSAccountNo],18) Select distinctAccount_id,#Update.FDMSAccountNo,External_ID into #sf from #Updateinner 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 #AddressFROM [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 allselect 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 allselect 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')xon 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 parentidfrom #Address inner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNoleft join #Update u1 on #Address.Parentsfid = u1.fdmsaccountnoorder by Open_Date desc -------------------------------------- --Finding Duplicates Mids --SELECT u.FDMSAccountNo, COUNT(*) from #Addressinner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNoleft join #Update u1 ON #Address.Parentsfid = u1.fdmsaccountnoGROUP BY u.FDMSAccountNoHAVING COUNT(*) > 1 --Finding Duplicates ACCOUNT IDS --SELECT u.FDMSAccountNo, U.ACCOUNT_IDfrom #Addressinner join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNoleft join #Update u1 ON #Address.Parentsfid = u1.fdmsaccountnoWHERE 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 dataMy 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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 :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 06:47:50
|
coolglad that you got it sorted------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-19 : 07:07:27
|
Thanks for the hint, it really helped |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-19 : 10:22:38
|
no problem you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|