March 17, 2006 at 2:59 pm
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.
March 17, 2006 at 3:10 pm
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
March 17, 2006 at 4:31 pm
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)
March 19, 2006 at 6:34 pm
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
March 22, 2006 at 5:06 pm
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