Representing an Organizational Structure

  • I am trying to represent an organizational structure for a company and admittedly need some help on where to begin.  Here is what I have.  I am working with two tables: en_entity and cn_contact. 

     

    In the en_entity table I the following id type fields:

     

    enid – This is the unique identifier of an entity.  It is the primary key in the table.

    orgid – This represents the enid of the entity that is at the top of the organizational structure.

    headid – This is the enid of the entity that the record is underneath or in essence reports to.

     

    In the cn_contact table I have the following id type fields:

    cnid – This is the unique identifier of a contact.  It is the primary key in the table.

    enid – This is the entity that the contact belongs to.

     

    What I am trying to do is write a query that will return the organizational structure by listing the organization and any contacts directly related to it and then any other entities that report to the organization and its contacts and so on and so on.  

     

    The CRM solution we use programmatically displays the organizational tree but I need to generate a text file or report to spit that out and am trying to do it in SQL.  My programming resources are limited so to try and get someone to make changes in our CRM system isn’t the best option right now.

     

    Hopefully, this is descriptive enough to get some ideas on where to begin.  Thanks in advance.

  • This actually is a common question in relational databases.  If you search here or on Google for heirarchical queries or bill-of-material queries, you can find many examples.  Also, a good resource is anything written by Joe Celko.  He describes and shows a number of methods to do this, for different structures and desired results.  He has described these in several books, articles here (and other DB sites), and his website.

    Hope this helps.



    Mark

  • I'll do a search on that but to give more clarrification on what I'm dealing with here is a simple example:

    Company A (enid = 1, orgid = 1, headid = 0)

                Bill (enid = 1, cnid = 1)

                Department A (enid = 2, orgid = 1, headid = 1)

                            Marie (enid = 2, cnid = 2)

                            Subdepartment A (enid = 3, orgid = 1, headid = 2)

                            Sue (enid = 3, cnid = 3)

     

  • This could be easily modified to fit your needs...  It's an old example and I've not made many changes to it.

    I used temp tables to keep from cluttering up my hard drive...  all but the scratchpad table could be permanent tables.  The #Employees table certainly represents a permanent table.

    I found it's easier to simply replace the table that ends up with the "bowers" (the hierarchical table) than to try to do inserts and other maintenance...

    The comments explain most of it and there are some example upline/downline lookups...

    --=======================================================================================

    --      Build some test data

    --=======================================================================================

    --===== Supress the auto-display of rowcounts for appearance and speed

        SET NOCOUNT ON

    --===== If experimental data table exists, drop it

         IF OBJECT_ID('TempDB..#Employees') IS NOT NULL

            DROP TABLE #Employees

    --===== Create an experimental table to represent a real table

     CREATE TABLE #Employees

            (

            ID             INT NOT NULL PRIMARY KEY,

            ParentID       INT,

            Name           VARCHAR(20),

            Salary         INT

            )

    --===== Populate the experimental table with sample data

     INSERT INTO #Employees

            (ID,ParentID,Name,Salary)

     SELECT 500,1000,'Chuck',1000 UNION ALL

     SELECT 75,500,'Sally',1000 UNION ALL

     SELECT 20,500,'John',1000 UNION ALL

     SELECT 60,75,'Jim',1000 UNION ALL

     SELECT 50,75,'Mary',1000 UNION ALL

     SELECT 10,20,'Sam',1000 UNION ALL

     SELECT 40,1000,'Bill',1000 UNION ALL

     SELECT 8,40,'Tom',1000 UNION ALL

     SELECT 9,500,'Linda',1000 UNION ALL

     SELECT 1000,Null,'Lori',1000

    --=======================================================================================

    -- Build the "tree"

    --=======================================================================================

    --===== If the table to hold the Hierarchical data exists, drop it

         IF OBJECT_ID('TempDB..#Hierarchy') IS NOT NULL

            DROP TABLE #Hierarchy

    --===== If the ScratchPad table exists, drop it

         IF OBJECT_ID('TempDB..#ScratchPad') IS NOT NULL

            DROP TABLE #ScratchPad

    --===== Create the Hierarchy table

     CREATE TABLE #Hierarchy

            (

            ID INT PRIMARY KEY,

            Level          INT,

            LeftBower      INT,

            RightBower     INT,

            Name           VARCHAR(40),

            Salary         INT,

            DownLineSalary INT,

            DownLineCount  INT

            )

    --===== Create the ScratchPad table

     CREATE TABLE #ScratchPad

            (

            ID       INT PRIMARY KEY,

            ParentID INT

            )

    --===== Populate the ScratchPad table with work to be done

     INSERT INTO #ScratchPad

            (ID,ParentID)

     SELECT ID,ParentID

       FROM #Employees

    --===== Declare Local Variables

    DECLARE @MyCounter INT

    DECLARE @MaxCount  INT

    DECLARE @Level     INT

    --===== Presets

       SET @MyCounter = 2

       SET @MaxCount  = 2 * (SELECT COUNT(*) FROM #Employees)

       SET @Level     = 1

    --===== Put the number one dog into the tree

     INSERT INTO #Hierarchy

            (Level, ID, LeftBower,RightBower)

     SELECT 1 AS Level,

            ID AS ID,

            1 AS LeftBower,

            NULL AS RightBower --Will be determined later

       FROM #Employees

      WHERE ParentID IS NULL

    --===== Build the tree

      WHILE @MyCounter <= (@MaxCount)

      BEGIN

            IF EXISTS ( -- See if anything left to do at this level

                       SELECT *

                         FROM #Hierarchy AS h,

                              #ScratchPad AS s

                        WHERE h.ID = s.ParentID

                          AND h.Level = @Level

                      )

            BEGIN   --===== Push when Level has subordinates, set LeftBower value

                     INSERT INTO #Hierarchy

                            (Level,ID,LeftBower,RightBower)

                     SELECT (@Level + 1) AS Level,

                            MIN(s.ID) AS ID,

                            @MyCounter AS LeftBower,

                            NULL AS RightBower --Will be determined on the way back up

                       FROM #Hierarchy AS h,

                            #ScratchPad AS s

                      WHERE h.ID = s.ParentID

                        AND h.Level = @Level

           

                    --===== Delete each item inserted in #Hierarchy

                     DELETE FROM #ScratchPad

                      WHERE ID = (SELECT ID

                                    FROM #Hierarchy

                                   WHERE Level = @Level + 1)

                    --===== Update the counters for the next item down      

                        SET @MyCounter = @MyCounter + 1

                        SET @Level = @Level + 1

            END

            ELSE

            BEGIN  --===== Pop the #Hierarchy and set RightBower value

                    UPDATE #Hierarchy

                       SET RightBower = @MyCounter,

                           Level = -Level -- pops the #Hierarchy

                     WHERE Level = @Level

                    --===== Update the counters for the next item up     

                        SET @MyCounter = @MyCounter + 1

                        SET @Level = @Level - 1

            END

        END --WHILE

    --===== Update the #Hierarchy table Level for positive numbers

         -- If any negatives continue to exist, then big problem.

         -- Also, insert the Name and Salary for reporting purposes.

     UPDATE #Hierarchy

        SET Level = -h.Level,

            Name = SPACE(2*(-h.Level-1))+p.Name,

            Salary = p.Salary

       FROM #Hierarchy h,

            #Employees p

      WHERE h.ID = p.ID

    --===== Insert the total salary of that person's downline (similar to upline, despite the name)

         -- and the total number of people working for that person including themselves

     UPDATE #Hierarchy

        SET DownLineSalary = other.DownLineSalary,

            DownLineCount  = other.DownLineCount

       FROM #Hierarchy h,

            (-- Derived table "other" finds the other info

             SELECT h2.ID,

                    SUM(ISNULL(h1.Salary,0)) AS DownLineSalary,

                    COUNT(h1.ID) AS DownLineCount

               FROM #Hierarchy AS h1,

                    #Hierarchy AS h2

              WHERE h1.LeftBower BETWEEN h2.LeftBower AND h2.RightBower

              GROUP BY h2.ID

            ) other

      WHERE h.ID = other.ID

    --=======================================================================================

    --      Demo some uses (these are incredibly fast for what they do)

    --=======================================================================================

    --===== Display the entire tree with Names indented by level

     SELECT h.*

       FROM #Hierarchy h

      ORDER BY h.LeftBower

    --===== Find the upline for "Sally"

      PRINT '===== Upline for "Sally" ====='

     SELECT h2.ID,

            h2.Level,

            SPACE(2*(h2.Level-1))+LTRIM(h2.Name) AS Name,

            h2.Salary,

            h2.DownLineSalary,

            h2.DownLineCount

       FROM #Hierarchy AS h1,

            #Hierarchy AS h2

      WHERE h1.LeftBower BETWEEN h2.LeftBower AND h2.RightBower

        AND h1.ID = 75 --<< ID of target employee and could be passed as a variable

      ORDER BY h2.LeftBower

    --===== Find the downline for "Sally"

         -- (Notice the very small change just in the table aliases of the table)

      PRINT '===== DownLine for "Sally" ====='

     SELECT h1.ID,

            h1.Level,

            SPACE(2*(h1.Level-h2.Level))+LTRIM(h1.Name) AS NAME,

            h1.Salary,

            h1.DownLineSalary,

            h1.DownLineCount

       FROM #Hierarchy AS h1,

            #Hierarchy AS h2

      WHERE h1.LeftBower BETWEEN h2.LeftBower AND h2.RightBower

        AND h2.ID = 75 --<< ID of target employee and could be passed as a variable

      ORDER BY h1.LeftBower

    --===== Find Lori's downline but only 2 levels down

      PRINT '===== DownLine for 2 levels for "Lori" ====='

     SELECT h1.ID,

            h1.Level,

            SPACE(2*(h1.Level-h2.Level))+LTRIM(h1.Name) AS NAME,

            h1.Salary,

            h1.DownLineSalary,

            h1.DownLineCount

       FROM #Hierarchy AS h1,

            #Hierarchy AS h2

      WHERE h1.LeftBower BETWEEN h2.LeftBower AND h2.RightBower

        AND h2.ID = 1000 --<< ID of target employee and could be passed as a variable

        AND h1.Level <= h2.Level+2 --<< Controls depth of search by level

      ORDER BY h1.LeftBower

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff!!  This really helped.  I was able to modify it and have it suit my needs.  Had some issues with getting the individual people's names (cn_contact table) to display the way I wanted but just accepted that and called it good enough.

    Thanks again Jeff.

Viewing 5 posts - 1 through 4 (of 4 total)

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