Author |
Topic |
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-19 : 07:29:11
|
HI I Thank you for all your responses over last couple of days, So far my SQL query is only returning records where the there is a match between two tables(which is due to the inner join, & i am happy this is now working correctly)I need to create a query, where Fdmsaccountno from #address is not found in #Update, 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],right([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--Cant Find this mid as not Salesforce, so wont match back to 502 ------select * from #sf----where FDMSAccountNo ='878325878881'--SELECT --FDMSAccountNo,COUNT(*), --Account_id--from #sf--GROUP BY FDMSAccountNo,Account_id--HAVING COUNT(*) < 2---- 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--new accounts are being identfied -- --select * from #Address--where fdmsaccountno = '878325878881'--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.fdmsaccountno--where u.FDMSAccountNo ='878325878881' -- account being dropped as not on SF -- order by Open_Date desc |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 07:40:00
|
>>I need to create a query, where Fdmsaccountno from #address is not found in #Update1) SELECT a.*FROM #Address aWHERE NOT EXISTS(SELECT 1FROM #Update WHERE Fdmsaccountno = a.Fdmsaccountno )2) SELECT a.Fdmsaccountno FROM #address aLEFT JOIN #Update u ON a.Fdmsaccountno = u.FdmsaccountnoWHERE u.Fdmsaccountno IS NULLYou can find different approaches by using this linkhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186211--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 07:45:01
|
As per you query, I think the red marked change is required--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 LEFT join #Update u on #Address.FDMSAccountNo = u.FDMSAccountNo left join #Update u1 on #Address.Parentsfid = u1.fdmsaccountno--where u.FDMSAccountNo ='878325878881' -- account being dropped as not on SF -- order by Open_Date desc --Chandu |
|
|
masond
Constraint Violating Yak Guru
447 Posts |
Posted - 2013-06-19 : 07:49:54
|
bandi Your knowledge is powerful :) |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-19 : 08:06:48
|
quote: Originally posted by masond bandi Your knowledge is powerful :)
--Chandu |
|
|
|
|
|