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
 Hierarchy Build Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 08/22/2013 :  11:20:40  Show Profile  Reply with Quote
Hi all I need some help

Aim- Find the next highest level for the child (Fdmsaccountno).
I have created query, however it doesn’t seem to be working correctly .The Query seems to work fine until Corp – To agent

Example –Correct Hierarchy --
Fdmsaccountno 878231250886
Chain 000000000000
Corporate 878231250886
Agent 878970035886

Results should look like
FDMSAccountNo External_ID Parentsfid
878231250886 520334502630767 878970035886

My query is

SELECT
stg_LMPAB502.FDMSAccountNo,
stg_LMPAB502.External_ID
,Parentsfid
FROM stg_LMPAB502
INNER JOIN stg_LMPAB501 ON stg_LMPAB502.FDMSAccountNo = stg_LMPAB501.FDMSAccountNo


---Outlet to chain --
inner join (
select distinct substring([MM-CHN-chain],2,12) as Parentsfid ,
FDMSAccountNo as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-chain],2,12) <> '000000000000'
and FDMSAccountNo <> substring([MM-CHN-CORP],2,12)
and FDMSAccountNo <> substring([MM-CHN-chain],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-chain],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-chain],2,12) = '000000000000'
or 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-chain],2,12) as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and FDMSAccountNo = substring([MM-CHN-chain],2,12)

-- Chain to agent--
union all
select distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,substring([MM-CHN-chain],2,12) as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-chain],2,12) <> '000000000000'
and substring([MM-CHN-CORP],2,12) = '000000000000'
and FDMSAccountNo = substring([MM-CHN-chain],2,12)

-- 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) <> [FDMSAccountNo]

) ---- Potential to change to fdms account no ----
x
on x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]
where [stg_LMPAB501].FDMSAccountNo ='878231250886'
and [stg_LMPAB501].FDMSAccountNo <> Parentsfid
  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.05 seconds. Powered By: Snitz Forums 2000