• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

SQL listing employees from a department level
#1
Hi!

This is my first topic, a pleasure to be part of this Community. We have been managing the tool for a short time and our first doubts arise.

We have the following departmental structure:

Company/Division/Area/Department

For example:

Company/Division A/Area A/Department 1

Company/Division A/Area A/Department 2

Company/Division A/Area B/Department1

We need to create a SQL listing question that shows a listing, last name field, of all active employees who "hang" from the second level, in our example case Division A.

is it viable? Some help?

Thank you very much
RaulAM, proud to be a member of EV CONNECT FORUM since Apr 2020.

#2
(07-07-2020, 07:31 AM)RaulAM Wrote: Hi!

This is my first topic, a pleasure to be part of this Community. We have been managing the tool for a short time and our first doubts arise.

We have the following departmental structure:

Company/Division/Area/Department

For example:

Company/Division A/Area A/Department 1

Company/Division A/Area A/Department 2

Company/Division A/Area B/Department1

We need to create a SQL listing question that shows a listing, last name field, of all active employees who "hang" from the second level, in our example case Division A.

is it viable? Some help?

Thank you very much

If you have it setup to populate, the easiest way to do what you are looking for would be to use the AM_DEPARTMENT_PATH table (if this is not setup to populate, you may need to reach out to support to help configure this for your database).  This table will have the DEPARTMENT_ID and the full path of each department in its tree structure.  You can join this table to your AM_EMPLOYEE table to get the list you need:
Code:
SELECT    AM_EMPLOYEE.LAST_NAME
    ,AM_DEPARTMENT_PATH.DEPARTMENT_PATH_EN
FROM    AM_EMPLOYEE
    LEFT JOIN AM_DEPARTMENT_PATH ON AM_EMPLOYEE.DEPARTMENT_ID = AM_DEPARTMENT_PATH.DEPARTMENT_ID
WHERE    (AM_EMPLOYEE.END_OF_CONTRACT > GETDATE() OR AM_EMPLOYEE.END_OF_CONTRACT IS NULL)
    AND AM_DEPARTMENT_PATH.DEPARTMENT_PATH_EN LIKE 'Company/Division A%'
Sarah Schumacher
Service Desk Tool Administrator

#3
Hi!

Thank you very much Sarah, the proposed solution is what I was looking for.

A cordial greeting.
RaulAM, proud to be a member of EV CONNECT FORUM since Apr 2020.






Users browsing this thread: 1 Guest(s)