Solved

List of all employees where I'm the manager of their department or some department above

Posted on 2012-04-03
2
174 Views
Last Modified: 2012-04-04
Problem:
I want a list of all employees where I'm the manager of their department or some department above (in MS SQL)
I can be a manager of one of more departments

Table 1: EMPLOYEES: columns employeecode, name, departmentcode
Table 2: MANAGERS:  columns employeecode, departmentcode (employeecode is manager of departmentcode)
Table 3: DEPARTMENTS: columns departmentcode, treecode
      treecode is a parent-child structure for each department, like
                 *TOP*parent*parent*parent*thisDepartment
      example: department 0310 has treecode *TOP*B05*B0003*0310*
      (0310's parent is B0003, B0003's parent is B05 and so on)

Example: if I am the manager of departments B05 and C01, I want all employees in any department having treecode LIKE '%*B05*%' or '%*C01*%' (MS SQL)
0
Comment
Question by:fmsol
2 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 37802748
Please try the following:
Select * 
  From Employees
Where DepartmentCode in
    (Select d2.DepartmentCode From Managers m
        join Departments d1 on d1.DepartmentCode = m.DepartmentCode
        join Departments d2 on d2.TreeCode Like d1.TreeCode + '%'
        where m.EmployeeCode = <MyEmployeeCode>)

Open in new window


I hope this helps.
0
 

Author Closing Comment

by:fmsol
ID: 37805009
Just a small change:
   join Departments d2 on d2.TreeCode Like '%*' + d1.TreeCode + '*%'

Thank you!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 55
SQL Syntax 24 46
Check ALL SP in database make sure there are no errors 17 44
Query for timesheet application 3 18
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question