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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 05/09/2013 :  10:12:45  Show Profile  Reply with Quote
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/


Edited by - masond on 05/09/2013 10:23:37

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/10/2013 :  00:41:38  Show Profile  Reply with Quote
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
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.08 seconds. Powered By: Snitz Forums 2000