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
 Excluding Results

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] 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
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 #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
Go to Top of Page

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 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 - 2013-06-19 : 07:49:54
bandi

Your knowledge is powerful :)
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -