CODEWITHSUNDEEP
sqlsql-server-2008hierarchy
user1588107
user1588107

Reputation: 1

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            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)
GO

CREATE TABLE dbo.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)
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)

Upvotes: 0

Views: 459

Answers (1)

HABO
HABO

Reputation: 15816

The problem appears to be an inability to specify how to determine that a child has children combined with a lack of identity for children. If the ON clause in the CTE could prevent a child joining to itself it would break the infinite recursion chain.

Sample output and answers to the comments should make it clear whether you simply need another condition in the ON clause or a UNION to handle childrens' children.

This at least stops recursing by limiting the hierarchy level and provides a testbed that anyone can execute without cluttering their database:

declare @Parent_Accounts as table (
  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) 

declare @Child_Accounts as table (
  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) 

; 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 @Parent_Accounts parent 
 WHERE parent.Account_Number = 2222481055218674 
 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 @Child_Accounts child 
 ON cte.Parent_Account_Key_Lookup = child.Child_Account_Key_Lookup and cte.Hierarchy_Level < 2) 
 SELECT * FROM cte_Recursive

Upvotes: 0

Related Questions