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
 Excluding Results
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/19/2013 :  07:29:11  Show Profile  Reply with Quote
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] 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


--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 #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

--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 parentid
from #Address
inner 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

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 06/19/2013 :  07:40:00  Show Profile  Reply with Quote
>>I need to create a query, where Fdmsaccountno from #address is not found in #Update
1) SELECT a.*
FROM #Address a
WHERE NOT EXISTS(
SELECT 1
FROM #Update
WHERE Fdmsaccountno = a.Fdmsaccountno )

2) SELECT a.Fdmsaccountno
FROM #address a
LEFT JOIN #Update u ON a.Fdmsaccountno = u.Fdmsaccountno
WHERE u.Fdmsaccountno IS NULL

You can find different approaches by using this link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186211

--
Chandu

Edited by - bandi on 06/19/2013 07:42:41
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 06/19/2013 :  07:45:01  Show Profile  Reply with Quote
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 parentid
from #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
Go to Top of Page

masond
Constraint Violating Yak Guru

447 Posts

Posted - 06/19/2013 :  07:49:54  Show Profile  Reply with Quote
bandi

Your knowledge is powerful :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 06/19/2013 :  08:06:48  Show Profile  Reply with Quote
quote:
Originally posted by masond

bandi

Your knowledge is powerful :)




--
Chandu
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.12 seconds. Powered By: Snitz Forums 2000