Account Hierarchy ? Parent account has children in which child could become a parent

  • I have 2 tables. One table contains all parent accounts, top level of hierarchy. Second table has all children accounts, that may or may not have a match to a parent account in the parent table. The goal is to create a query (SQL Server 2008, recursive or non) that finds all child accounts that match to the parent in addition to the fact that the child could itself be a parent to other child accounts.

    In simpler terms, once a match has been made on a parent to child, need to check to make sure that the child in the match is not itself a parent to other child accounts. A mouthful I understand and I hope it makes sense. I also do not know the depth of which the hierarchy would extend.

    CREATE TABLE dbo.Parent_Accounts

    (Parent_Account_Key_Lookup varchar(28) NOT NULL,

    Account_Number bigintNOT NULL,

    Reference_Account_Number_1 bigintNOT NULL,

    Reference_Account_Number_2 bigintNOT NULL,

    OpenDate intNOT NULL,

    Status char(1) NOT NULL,

    Record_Created smalldatetimeNOT NULL,

    Active bitNOT NULL)

    GO

    CREATE TABLE dbo.Child_Accounts

    (Child_Account_Key_Lookup varchar(28) NOT NULL,

    Account_Number bigintNOT NULL,

    Reference_Account_Number_1 bigintNOT NULL,

    Reference_Account_Number_2 bigintNOT NULL,

    OpenDate intNOT NULL,

    Status char(1) NOT NULL,

    Record_Created smalldatetimeNOT NULL,

    Active bitNOT NULL)

    GO

    WITH cte_Recursive

    AS(SELECT parent.Account_Number,

    parent.Parent_Account_Key_Lookup,

    parent.Reference_Account_Number_1,

    parent.Reference_Account_Number_2,

    parent.OpenDate,

    parent.[Status],

    parent.Record_Created,

    parent.Active,

    1 AS Hierarchy_Level

    FROM dbo.Parent_Accounts parent

    WHERE parent.Account_Number = 4498481055218674

    UNION ALL

    SELECT child.Account_Number,

    child.Child_Account_Key_Lookup,

    child.Reference_Account_Number_1,

    child.Reference_Account_Number_2,

    child.OpenDate,

    child.[Status],

    child.Record_Created,

    child.Active,

    cte.Hierarchy_Level + 1

    FROM cte_Recursive cte

    INNER JOIN dbo.Child_Accounts child

    ON cte.Parent_Account_Key_Lookup = child.Child_Account_Key_Lookup)

    --SELECT * FROM cte_Recursive

    SELECT TOP 2 * FROM cte_Recursive

    INSERT INTO dbo.Parent_Accounts

    (Parent_Account_Key_Lookup,

    Account_Number,

    Reference_Account_Number_1,

    Reference_Account_Number_2,

    OpenDate,

    [Status],

    Record_Created,

    Active)

    VALUES ('222248105521867419970702', 2222481055218674, 2222481060975466, 0, 19970702, 'U', '2010-11-18 12:46:00', 0)

    INSERT INTO dbo.Child_Accounts

    (Child_Account_Key_Lookup,

    Account_Number,

    Reference_Account_Number_1,

    Reference_Account_Number_2,

    OpenDate,

    [Status],

    Record_Created,

    Active)

    VALUES ('222248105521867419970702', 2222481060975466, 2222481055218674, 2222481055218674, 19970702, 'L', '2010-11-19 08:33:00', 0),

    ('222248106097546619970702', 2222481060982900, 2222481060989137, 2222481060975466, 19970702, 'U', '2010-11-19 16:54:00', 0),

    ('222248106098290019970702', 2222481060989137, 0, 2222481060982900, 19970702, ' ', '2010-11-21 01:52:00', 1)

    If you run each of the select statements from the cte you will see the issue. I have included an attachment of what I am trying to accomplish.

  • Question:

    Do you have to have two tables? Is it possible for child accounts to have more than one parent?

    Erin

  • I do not have to have 2 tables. Was just to keep my sanity. Answer to other question was yes.

  • Let me understand this.. you have a many-to-many relationship (parent-child and child-parent) and you'd like to pull a list of all parents and their children only, no grandchildren.

  • Yes. I believe so.

  • Does the attachment I included make sense. I am trying to track how an account number changes over time, but as they do change there is still a root account that they all derived from.

  • With the example you show in the attachment I don't see how a single account can have multiple parents. If it's not possible for an account to have multiple parent accounts concurrently then I would simply add a hierarchy column to one table to show which parent a child is associated.

  • Determined from what table the account number was selected from, parent or child. If I am looking at this incorrectly, please let me know as this has me baffled.

  • Good advice, Celko. I'm just hoping this isn't an architecture that he's inherited and can make some structural changes or additions.

    Erin

  • I am open to suggestions. Could you provide an example please? Nothing fancy just an idea.

  • Can you provide some more sample data of the following?

    PARENT

    / CHILD1 CHILD2

    / \ / CHILD3 CHILD4CHILD5CHILD6

  • I can get you started but I have a question. Your rCTE looked close but I've made some modifications that I'll explain in a minute. First a revised data setup to use temp tables (my preference):

    CREATE TABLE #Parent_Accounts

    (Parent_Account_Key_Lookup varchar(28) NOT NULL,

    Account_Number bigint NOT NULL,

    Reference_Account_Number_1 bigint NOT NULL,

    Reference_Account_Number_2 bigint NOT NULL,

    OpenDate int NOT NULL,

    Status char(1) NOT NULL,

    Record_Created smalldatetime NOT NULL,

    Active bit NOT NULL)

    CREATE TABLE #Child_Accounts

    (Child_Account_Key_Lookup varchar(28) NOT NULL,

    Account_Number bigint NOT NULL,

    Reference_Account_Number_1 bigint NOT NULL,

    Reference_Account_Number_2 bigint NOT NULL,

    OpenDate int NOT NULL,

    Status char(1) NOT NULL,

    Record_Created smalldatetime NOT NULL,

    Active bit NOT NULL)

    INSERT INTO #Parent_Accounts

    (Parent_Account_Key_Lookup,

    Account_Number,

    Reference_Account_Number_1,

    Reference_Account_Number_2,

    OpenDate,

    [Status],

    Record_Created,

    Active)

    VALUES ('222248105521867419970702', 2222481055218674, 2222481060975466, 0, 19970702, 'U', '2010-11-18 12:46:00', 0)

    INSERT INTO #Child_Accounts

    (Child_Account_Key_Lookup,

    Account_Number,

    Reference_Account_Number_1,

    Reference_Account_Number_2,

    OpenDate,

    [Status],

    Record_Created,

    Active)

    VALUES ('222248105521867419970702', 2222481060975466, 2222481055218674, 2222481055218674, 19970702, 'L', '2010-11-19 08:33:00', 0),

    ('222248106097546619970702', 2222481060982900, 2222481060989137, 2222481060975466, 19970702, 'U', '2010-11-19 16:54:00', 0),

    ('222248106098290019970702', 2222481060989137, 0, 2222481060982900, 19970702, ' ', '2010-11-21 01:52:00', 1)

    SELECT * FROM #Parent_Accounts

    SELECT * FROM #Child_Accounts

    Now, in my version of the rCTE (below), I've made the anchor leg of the rCTE match parent accounts (from that table) with child accounts.

    ;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 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.Parent_Account_Key_Lookup = c.Child_Account_Key_Lookup

    )

    SELECT *

    FROM cte_Recursive

    DROP TABLE #Parent_Accounts

    DROP TABLE #Child_Accounts

    The recursive leg (commented out) needs to match any child account that itself has a child. What I couldn't figure out from your data is exactly how that relationship is reckoned. This may require two recursive legs - the first to resolve the initial parents and then the second to resolve children of the child parents.

    Did this help?

    Ya gotta love dem recursive CTEs!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • I am intrigued. How do you build two recursive legs within one cte? You have my attention.

  • ;WITH rCTE AS (

    -- Anchor 1: ...

    SELECT ...

    UNION ALL

    -- Anchor 2: ...

    SELECT ...

    UNION ALL

    -- Recursive 1: ...

    SELECT ...

    UNION ALL

    -- Recursive 2: ...

    SELECT ...

    )

    SELECT * FROM rCTE

    You can have as many anchor and recursive legs as you need... all separated by UNION ALL.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • 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.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply