Recursive CTE - 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

Recursive CTE

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-05-09 : 10:12:45

Hey guys,
I don’t use this forum for ages, and then i have two problems in a day which has baffled me
Aim – Creating a hierarchy structure
Agent – Highest Level
Corp- Second Highest Level
Chain – Third Highest Level
FDMS AccountNO – Fourth Highest Level

Within my data i always have a Agent, However i don’t always have a corp, so what i need is a query that gets the chain number, and puts the chain number into the corp , and fdmsaccount no into chain,
For eg 1
Agent_Chain_No Corp_Chain_No Chain_Chain_No FDMSAccountNo
878970001888 000000000000 000000000000 878000000884
In this scenario the FDMSaccount would be the Corp Chain No
Eg 2
Agent_Chain_No Corp_Chain_No Chain_Chain_No FDMSAccountNo
878970001888 878003000881 000000000000 878003000881
In this scenario the FDMSaccount would be the Chain Chain No

Eg 3
Agent_Chain_No Corp_Chain_No Chain_Chain_No FDMSAccountNo
878970001888 000000000000 878230737883 878000004886
In this scenario the Chain No would be the CorpChain No , and fdmsaccount no would be chain no

Hope this makes sense
This is my table
SELECT
[Agent_Chain_No]
,[Corp_Chain_No]
,[Chain_Chain_No]
,[FDMSAccountNo]
FROM [FDMS].[dbo].[Dim_Outlet]

i am trying to achieve the same as in this link

http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-10 : 00:41:38

I think best approach would be do this as a series of updates each covering different scenario

something like


UPDATE Table
SET Corp_Chain_No  = FDMSAccountNo
WHERE Corp_Chain_No='000000000000'
AND Chain_Chain_No = '000000000000'

UPDATE Table
SET Chain_Chain_No = FDMSAccountNo
WHERE Corp_Chain_No <> '000000000000'
AND Chain_Chain_No = '000000000000'


UPDATE Table
SET Corp_Chain_No  = Chain_Chain_No,
Chain_Chain_No = FDMSAccountNo
WHERE Corp_Chain_No='000000000000'
AND Chain_Chain_No <> '000000000000'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources