SQL for finding counts of employees reporting under supervisor

Shasidhar, for several clients I have used SQL (for SQL Server but not Oracle) to do hierarchical listings of employees up and down the supervisor chain. What I had to do was write everything into a temp table, using recursion to work up and down the chain, and then return the contents of the temp table as the resultset. It was indeed very difficult, but it was achievable.

Check out the "START WITH...CONNECT BY" clause for hierarchical queries in Oracle.

Hi Chris,

I did get this query from our DBA, however i unable to use this query on the HRSUPER table. However this would work if i RECREATE the hierarchy in a temp table. Is this is the only option i have ? since if it is a temp table i have to write PL/SQL that is run daily to dynamically insert parent-child recs in the temp table.I am trying to find out if there is a way i could use HRSUPER to get the hierarchy because ultimatly i am only concerned about pulling the count of employees direct/indirect if they exist for each employee.

regards,
Shasi

- I built the procedure to re-create, load, then remove the temp table each time the sproc ran.
- Are you looking for counts all the way down the hierarchy for a given employee, or just his/her direct/indirect reports?

Are you using this as a datasource for a Crystal report?

John : I am looking for counts all the way down the hierarchy for a given employee. i have to loop through all active employees, if a employee is not a supervisor the count is 0.

Chris : You are absolutly correct, The logic i am trying to develop is to populate a field in a non-lawson table that will eventually be used by a Crystal report in LBI.