Hierarchy Build Help - SQL Server Forums

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

Hierarchy Build Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-22 : 11:20:40

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources