SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 seeking/traversing through a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 04/04/2013 :  16:46:29  Show Profile  Reply with Quote
I have a table that looks like this (the actual table has over 3 mln recs):

FIRST_NAME LAST_NAME RAW_ID sup_RAW_id (SUPERVISOR) sup_mgr_RAW_ID (SUPERVISOR of the MANAGER) BUS
JOHN SMITH L101185 P787997 K546899 GHH
GEORGE GREEN P021144 L566444 K756565 GHH
MICHAEL RED L323335 L322342 P343243 GHH
JAMES BLACK P212121 P925513 P247323 GHH

I am tired of maintaining a static table with folks names. I am trying to come up with SQL that would enable me to traverse through this table and come back with "DIRECT" names for each employee. DIRECT is an upper level manager. For some folks it is 3 hoerarchical steps away for some 4 etc. What I mean by DIRECT is that each employee ex. JOHN SMITH, has manager and manager's manager is someone, and that manager is manager's manager of someone (the organization is using hirerchical managerial structure, like a tree) . Knowing that teh top of my organization is managed by Z111111 (All directs report to Z111111) how I can link JOHN SMITH or JAMES BLACK to directly DIRECT who works for Z111111? Skipping all medium level managers?

Sample output would be:
FIRST_NAME LAST_NAME RAW_ID sup_RAW_id (SUPERVISOR) sup_mgr_RAW_ID (SUPERVISOR of the MANAGER) BUS DIRECT
JOHN SMITH L101185 P787997 K546899 GHH R434333
GEORGE GREEN P021144 L566444 K756565 GHH Y000002
MICHAEL RED L323335 L322342 P343243 GHH E232323
JAMES BLACK P212121 P925513 P247323 GHH W22222


note: all employees like JOHN SMITH etc must have manager and their manager must have manager and so on.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/05/2013 :  01:10:16  Show Profile  Reply with Quote
you can use a recursive CTE for that

you just need employee and managerid (not even his managers) to climb up ladder and get final superior

see similar example here

http://msdn.microsoft.com/en-IN/library/ms186243(v=sql.105).aspx



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 04/08/2013 :  18:00:51  Show Profile  Reply with Quote
Thank you visakh16 - I went with your suggestion and implemented it. I just got rid off unnecessary INNER JOIN statements.

Edited by - nietzky on 04/08/2013 18:01:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/09/2013 :  00:17:01  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000