April 18, 2007 at 12:28 am
Hi,
I want to know the reporting percentage of employees under one manager.
For example, one manager has 3 resources reporting to him.
and one of resource under the manager has one resource reporting to him
so in this case i need indirect reporting of top manager. Can anybody suggest the sample query for this.
Thanks in advance
Madhuri
April 19, 2007 at 5:26 am
Could you post your table structure and some sample data please? It's a little difficult to write a working query without them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2007 at 6:44 am
Hi,
I have a table named resource.
the table structure as follows
Resource
--------
UserID
Name
ManagerId
Sample Data
--------------
UserID | Name | ManagerID
5000 Sandeep 10000
5001 Saket 10000
5002 Rahul 10000
10000 Bindu 1
1 Admin 1
6001 Test1 5000
6002 Test2 5000
7001 Test3 5001
Query i wrote to get resourcecount
-----------------------------------
SELECT r.manager_id,manager.Resourename as ResourceManager,count(*) as resourcecount
FROM Resources r inner join Resources manager
on r.manager_id = manager.user_id
group by r.manager_id,manager.full_name
order by r.manager_id,manager.full_name
I am able to get the resourcecount under particular manager but i need to get indirect reporting count.
For example, Bindu has 3 resources reporting to her,sandeep has 2 resources reporting to him and saket has 1 resource reporting to him. Finally for Bindu resource count should come as 6
Thanks,
Madhuri
April 19, 2007 at 7:03 am
Are you using SQL 2000 or 2005? It's very easy on 2005 but on 2000 it can be quite tricky.
How deep can the hierarchy go?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2007 at 10:59 pm
I am using sql server 2000. Is it possible to do in sql server 2000?
Thanks,
Madhuri
April 19, 2007 at 11:08 pm
Yes, but will either need a cursor or a hard-coded limit on how deep the hierarchy will go.
Which is your preference?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2007 at 11:14 pm
Table has so many rows i can't use hard-coded limit on this. If table has less rows then that is feasible way.I think cursor is better option to go. What is your suggestion? If possible please provide sample query how to use cursor for this?
Thanks for reply
Madhuri
April 20, 2007 at 8:17 am
There is an excellent article about hierarchies in Joe Clecko's book SQL For Smarties. But it involves some upfront work on the resource table. If you can alter the database schema to add a couple of fields to the resource table, It would make the query for indirect reporting a snap. Joe's suggestion is to add integer fields called left and right. Then use a stored procedure to populate the fields, and perhaps set up triggers to maintain them.
The table ends up looking something like this
EmployeeID EmployeeName ReportsTo Left Right
1 Sally <null> 0 9
2 John 1 1 2
3 Tim 1 3 8
4 Chuck 3 4 5
5 George 3 6 7
Counting indirect reporting resources becomes ((right -Left)-1)/2
Finding all employees that report to Tim means writing a query for employees where the left value is greater than 3 and right value is less than 8.
I use this approach for sewer mains and finding all mains upstream of a particular manhole. I just run the stored procedure every night and didn't have to implement any triggers. If you can get a copy of the book it is highly recomended at least by me.
April 20, 2007 at 9:15 am
Here is a very good article with sample code that was referenced in another post about heirarchical data:
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply