I am working on a DTS package to create an organizational chart in SQL Server 2000.
The table where I get my data from is called WebPhone. WebPhone contains 5,753
employees (records). Each record has a unique identifier called a UserID. Each
record also has a SupervisorID so I know who they report to. What I am trying to
accomplish is a package that will build a table for the entire organization so I don�t
have to make my pages in ColdFusion do it. I figure build the table nightly and be
done with it.
There are several different levels (titles) of employees. VicePresident,
GeneralManager, Director, AreaManager, Manager.
I needed to determine the different levels so I broke the employees out into several
tables depending on their title based on the titles listed above. With these tables
built, I ran a SQL query (as seen below) that obtained who reports to whom in the
organizational structure.
SELECT webphone_mgr.mgr_sbcuid,
webphone_mgr.mgr_sup_id,
webphone_am.am_sbcuid,
webphone_am.am_sup_id,
webphone_dir.dir_sbcuid,
webphone_dir.dir_sup_id,
webphone_gm.gm_sbcuid,
webphone_gm.gm_sup_id,
webphone_vp.vp_sbcuid,
webphone_vp.vp_sup_id,
FROM ((((webphone_vp INNER JOIN
webphone_gm ON
webphone_vp.vp_sbcuid = webphone_gm.gm_sup_id) INNER JOIN
webphone_dir ON
webphone_gm.gm_sbcuid = webphone_dir.dir_sup_id) INNER JOIN
webphone_am ON
webphone_dir.dir_sbcuid = webphone_am.am_sup_id) INNER JOIN
webphone_mgr ON
webphone_am.am_sbcuid = webphone_mgr.mgr_sup_id)
The problem that I am having is that it can skip levels. Everyone reports to the same
VP, however you can have a Director who doesn�t report to a GM, rather they report to
the VP directly. I tried to overcome the skipping by running similar queries for the
VP at all levels. This worked like a charm, but I have the same problem at every
level. Then I begin seeing duplicates.
This is really a logic question more than a coding issue. Any suggestions would be
appreacited!
Thanks!
Cham Bell
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/sql
Get the mailserver that powers this list at http://www.coolfusion.com
