Please help me to create the sample query

Question

Please help me to create the sample query

madhuri-425013

Say Hey Kid

Points: 679
More actions
April 18, 2007 at 12:28 am

#195120

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

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 4

I am using sql server 2000. Is it possible to do in sql server 2000?

Thanks,

Madhuri

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 6

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

Todd Payne SSC-Addicted Points: 458 More actions · Answer 7

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.

SoCal_DBD Hall of Fame Points: 3111 More actions · Answer 8

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