August 9, 2012 at 7:50 pm
In thinking about it a little further and a little clearer, I think what you need is this:
1. Anchor 1: Resolve parents --> children
2. Anchor 2: Resolve children (that are parents) --> their children
3. Recursive 1: Resolve grandchildren (and all that follow) --> their children
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 7:57 pm
Brandon Carl Goodman (8/9/2012)
If Reference_Account_Number_2 is 0 this would be a parent account as there are no other accounts associated with it at this point. A child account would be an account number was populated with an account number. I tried to depict that with my image that is attached to the post. I am able to get the first 2 levels of the hierarchy however it is when the account number needs to become a parent to again check to see if any other accounts are attached. A daisy chain if you will.
With that additional explanation, you can try this:
;WITH cte_Recursive AS (
-- Anchor leg (1): List out all the parents with children
SELECT p.Account_Number,
p.Parent_Account_Key_Lookup,
p.Reference_Account_Number_1,
p.Reference_Account_Number_2,
p.OpenDate,
p.[Status],
p.Record_Created,
p.Active,
1 AS Hierarchy_Level
FROM #Parent_Accounts p
INNER JOIN #Child_Accounts c
ON p.Parent_Account_Key_Lookup = c.Child_Account_Key_Lookup
UNION ALL
SELECT p.Account_Number,
p.Child_Account_Key_Lookup,
p.Reference_Account_Number_1,
p.Reference_Account_Number_2,
p.OpenDate,
p.[Status],
p.Record_Created,
p.Active,
1 AS Hierarchy_Level
FROM #Child_Accounts p
INNER JOIN #Child_Accounts c
ON p.Child_Account_Key_Lookup = c.Child_Account_Key_Lookup
WHERE p.Reference_Account_Number_1 = 0
UNION ALL
SELECT c.Account_Number,
c.Child_Account_Key_Lookup,
c.Reference_Account_Number_1,
c.Reference_Account_Number_2,
c.OpenDate,
c.[Status],
c.Record_Created,
c.Active,
p.Hierarchy_Level + 1
FROM cte_Recursive p
INNER JOIN #Child_Accounts c
ON p.Account_Number = c.Reference_Account_Number_2
--WHERE p.Hierarchy_Level < 3
)
SELECT *
FROM cte_Recursive
Note the commented out --WHERE p.Hierarchy_Level < 3 is used in testing to avoid recursion depth errors.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 8:04 pm
My friend, if you are ever in the Des Moines, Iowa area, I owe you a beer or a glass of wine, whatever your preference may be. Please let me wrap my head around what you have provided and I will respond back very soon. I appreciate your patience and your time.
As a side question, the depth at which the accounts could go is unknown. May this become a problem with the max recursion?
August 9, 2012 at 8:12 pm
Brandon Carl Goodman (8/9/2012)
My friend, if you are ever in the Des Moines, Iowa area, I owe you a beer or a glass of wine, whatever your preference may be. Please let me wrap my head around what you have provided and I will respond back very soon. I appreciate your patience and your time.As a side question, the depth at which the accounts could go is unknown. May this become a problem with the max recursion?
I live in Bangkok now and don't get back to the states often but I'll keep your offer in mind. 😀
The max number of recursions is 100 but you can override this by adding
OPTION (MAXRECURSION 0) -- No limit
But I must day you'd have to have one freakin' messed up COA if it ends up going to depth 100!
BTW. In case you're wondering how I was able to knock this out so quickly, I spent some time learning about rCTEs on my own. I found that information on the Internet sucked in terms of examples. So I devised my own and authored an article on this site about my learning experience:
http://www.sqlservercentral.com/articles/T-SQL/90955/
If you work through the examples in there, you'll easily become quite adept at them yourself in short order.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 8:20 pm
CELKO (8/9/2012)
This is design is wrong. A Chart of Accounts keeps its hierarchy in the encoding; think of the Dewey Decimal system. Your design flaw is called "Attribute Splitting" and it is common.
Joe - First of all, I'm not one of the "jump on the bandwagon and beat on Joe Celko" folks as I have a great respect for your knowledge and your accomplishments.
But I must ask a basic question about this response from you. Whatever happened to the notion that you should not build intelligence into encoded data?
I know that it is done a lot, particularly in COAs. Clearly by simply grouping the accounts by numbering into categories you are doing so. But from a theoretical standpoint (which is where I see a lot of your criticism come from) shouldn't this be avoided?
I'm wondering if libraries are now regretting acceptance of Dewey's decimal system.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 9, 2012 at 8:34 pm
I appreciate the link. Bangkok, never been that far east. Like stated before, I would like to wrap my head around this before I ask some questions.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply