Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: Cumulative related records from hierarchy

    Morning chucky eggs

    This is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798

    SQL 2k5

    I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.

    Below is
    1) Set up code (in red)
    2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly
    3) My new soulution.

    My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.

    Code:
    USE tempdb
    go
    
    ---------------------------------------------------------------
    --        Set up the structure & data...
    ---------------------------------------------------------------
    CREATE TABLE dbo.org
        (
            unit_code        VARCHAR(10)    NOT NULL
            , parent_code    VARCHAR(10)    NULL
            , CONSTRAINT pk_org    PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
        )
    GO    
    
    CREATE TABLE dbo.org_peeps
        (
            unit_code        VARCHAR(10)    NOT NULL
            , person_code    INT            NOT NULL
            , CONSTRAINT pk_org_peeps    PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
            , CONSTRAINT fk_org_peeps_other_org_peeps    FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
        )
    GO    
    
    CREATE VIEW dbo.org_peeps_parents
    --WITH SCHEMABINDING
    AS
        SELECT      org.unit_code
                , org.parent_code
                , org_peeps.person_code
        FROM    dbo.org
        LEFT OUTER JOIN 
                dbo.org_peeps
        ON    org_peeps.unit_code        = org.unit_code
    GO    
    
    INSERT INTO dbo.org (unit_code, parent_code)
    SELECT    'a', 'y'        UNION ALL
    SELECT    'b', 'y'        UNION ALL
    SELECT    'c', 'z'        UNION ALL
    SELECT    'y', 'z'        UNION ALL
    SELECT    'z', NULL
    
    --Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
    INSERT INTO dbo.org_peeps (unit_code, person_code)
    SELECT    'a', 1    UNION ALL
    SELECT    'c', 3    UNION ALL
    --These data go into intermediate levels of the organisation...
    SELECT    'y', 4    UNION ALL
    SELECT    'y', 5    UNION ALL
    SELECT    'y', 6
    
    ---------------------------------------------------------------
    SELECT    'Original query. Because there is data in the intermediate tables --> ' +
            'duplicate outputs....'
    ---------------------------------------------------------------
    ;WITH    materialised_paths
    AS
        (
            SELECT    unit_code
                    , parent_code
                    , person_code
                    , unit_path        = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
            FROM    dbo.org_peeps_parents
            WHERE    parent_code IS NULL
            UNION ALL
            SELECT    all_people.unit_code
                    , all_people.parent_code
                    , all_people.person_code
                    , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
            FROM    dbo.org_peeps_parents AS all_people
            INNER JOIN
                    materialised_paths AS mp 
            ON    mp.unit_code        = all_people.parent_code
        )
    SELECT    parents.unit_code
            , children.person_code
    FROM    materialised_paths AS children
    CROSS APPLY    --Correlated derived table - get the child records per unit
            (
                SELECT    unit_code
                        , parent_code
                FROM    dbo.org        AS parents_sub
                WHERE    children.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
            ) AS parents
    WHERE    children.person_code IS NOT NULL
    ORDER BY person_code
            , unit_code
    
    -----------------------------------------------------------------
    SELECT    'This query returns the correct results but man it is ugly. '+
                'Can it be refined\ optimised?'
    -----------------------------------------------------------------
    ;WITH    materialised_paths
    AS
        (
            SELECT    unit_code
                    , parent_code
                    , unit_path        = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
            FROM    dbo.org
            WHERE    parent_code IS NULL
            UNION ALL
            SELECT    all_orgs.unit_code
                    , all_orgs.parent_code
                    , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/'
            FROM    dbo.org AS all_orgs
            INNER JOIN
                    materialised_paths AS mp 
            ON    mp.unit_code        = all_orgs.parent_code
        )
    SELECT    all_orgs.unit_code
            , org_peeps.person_code
    FROM    dbo.org_peeps
    INNER JOIN 
            (
                SELECT    org.unit_code
                        , mp.unit_path
                FROM    materialised_paths AS mp
                CROSS APPLY
                        (
                            SELECT    unit_code
                                    , parent_code
                            FROM    dbo.org
                            WHERE    mp.unit_path LIKE '%/' + org.unit_code + '/%'
                        ) AS org
            ) AS all_orgs
    ON    all_orgs.unit_path        LIKE '%/' + org_peeps.unit_code + '/'
    ORDER BY person_code
            , all_orgs.unit_code
    
    --Clean up
    IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
        DROP VIEW dbo.org_peeps_parents
    END
    
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
        DROP TABLE dbo.org_peeps
    END
    IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
        DROP TABLE dbo.org
    END
    As ever kudos and lavish thanks to anyone that can help

    Oh - I nearly forgot - accounting for the Rudy clause - desired output:
    Code:
    unit_code  person_code
    ---------- -----------
    a          1
    y          1
    z          1
    c          3
    z          3
    y          4
    z          4
    y          5
    z          5
    y          6
    z          6

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm not going to lie to you; I really don't understand what you're trying to achieve...

    Is it something along the lines of
    development team = 10 people
    ict = 50 people
    corporate division = 200 people

    results
    development team = 10
    ict = 10 + 50 = 60
    corporate division = 10 + 50 + 200 = 260

    ??
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I'm not going to lie to you
    Good - I would know

    Exactly that except I don't want the aggregates - I want the actual person records. So there would be 10 records against dev team, 60 against ict and 260 against corporate division.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh wait, now I've doodled the diagram it makes sense... sort of
    Code:
        Z
       / \
      /   \
     /     \
    C-3     Y-456
           / \
          /   \
         /     \
        A-1     B
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's the structure in the tables. Desired results:
    Code:
        Z-45631
       / \
      /   \
     /     \
    C-3     Y-4561
           / \
          /   \
         /     \
        A-1     B

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hang on a minute... How can z have a value of 6 when there are only 5 employees?

    Dear me, I don't think I'm going to get this one
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Z does not "have a value of 6". Those are not counts - they are just surrrogate keys representing people.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, back to basics for a moment... What are you trying to achieve and why? You've presented us (read: me) with a near solution to a problem that's not entirely clear [to me].

    Ugh, I feel like a newb again.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh dear me - thread hijacked

    I want every person returned against all the business units that they are part of (i.e. their own business unit and all the parent business units). You work for Development Team, and are recorded against that in your HR db. Development Team is a part of IT Division so you effectively work for that too, it is just not recorded directly in the HR system. You also work for XYZ Corp. So if I ran the query on you:

    Code:
    Development Team    George
    IT Division        George
    XYZ Corp        George
    So the database only records that you are part of Development Team. It also only records that IT Division is the parent of Development Team and XYZ Corp is the parent of IT Division, hence your membership of those business units can be inferred.

    How's that for ya?

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And you want to cover the whole business in one query, or a single child > parent route?
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Whole business baby yeah!

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this any better?
    Code:
    USE playdb
    
    CREATE TABLE dbo.org (
       org_place   char(1)     PRIMARY KEY CLUSTERED
     , parent      char(1)     NULL
    )
    
    CREATE TABLE dbo.employees (
       employee_id int         NOT NULL
     , org_place   char(1)     FOREIGN KEY REFERENCES org(org_place)
    )
    
    INSERT INTO dbo.org (org_place, parent)
          SELECT 'Z',  NULL
    UNION SELECT 'C', 'Z'
    UNION SELECT 'Y', 'Z'
    UNION SELECT 'A', 'Y'
    UNION SELECT 'B', 'Y'
    
    INSERT INTO dbo.employees (employee_id, org_place)
          SELECT 1, 'A'
    UNION SELECT 3, 'C'
    UNION SELECT 4, 'Y'
    UNION SELECT 5, 'Y'
    UNION SELECT 6, 'Y'
    
    ; WITH myCTE AS (
      SELECT e.employee_id
           , o.org_place
           , o.parent
      FROM   dbo.employees e
       INNER
        JOIN dbo.org o
          ON e.org_place = o.org_place
    
      UNION ALL
    
      SELECT e.employee_id
           , o.org_place
           , o.parent
      FROM   myCTE e
       INNER
        JOIN dbo.org o
          ON e.parent = o.org_place
    )
    
    SELECT employee_id, org_place FROM myCTE
    ORDER BY 1,2
    
    GO
    IF object_id('dbo.employees') IS NOT NULL BEGIN
      DROP TABLE dbo.employees
    END
    IF object_id('dbo.org') IS NOT NULL BEGIN
      DROP TABLE dbo.org
    END
    Code:
    employee_id org_place 
    ----------- --------- 
    1           A
    1           Y
    1           Z
    3           C
    3           Z
    4           Y
    4           Z
    5           Y
    5           Z
    6           Y
    6           Z
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to give us an update..?
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In a meeting - done some testing....

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ah sorry, I just wanted to know because I'm really chuffed that I've come up with that solution on my lonesome - I'm new to CTE's so it's a biggie for me



    ...I need to get out more
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •