Retrieving inactive employees

Question

I have the following query using the Invantive Query Tool connecting to NMBRS.

select e.number
,      es.EmployeeId  
,      e.displayname
,      es.ParttimePercentage
,      es.startdate
from   Nmbrs.Employees.EmployeeSchedules es
left 
outer 
join   Nmbrs.Employees.Employees e 
on     es.EmployeeId = e.id 
order 
by     e.displayname
,      es.startdate

(I want to retrieve all mutations in part-time percentage/schedule)

However Nmbrs.Employees.Employees only shows active employees. And I need that because that shows the employee ID as shown in Nmbrs.Employees.EmployeeSchedules is not the employee ID shown in the UI rather it is an internal ID.

I did notice Nmbrs.Employees.Employees has an additional where clause (as per documentation):

Additional Where Clause:
- CompanyId
- active

The following query

select * from Nmbrs.Employees.Employees where active = 1

gives an error:

Unknown identifier 'active'.

Consider one of the following: Nmbrs.Employees.Employees.PartitionID,     Nmbrs.Employees.Employees.Id, Nmbrs.Employees.Employees.Number,     Nmbrs.Employees.Employees.DisplayName, Employees.Employees.PartitionID, Employees.PartitionID, PartitionID, Employees.Employees.Id.

Active isn't mentioned so I don't know if that is usable.

Do you want to retrieve the inactive employees and are they currently in the view? Or do you now have the active ones and you want the inactive to be included also? — Guido Leenders, Jun 19 '18 at 16:10
I have only the active ones now and want active and inactive employees. I need to have all changes in part-time percentage last year for all employees that have worked last year. — RS Finance, Jun 20 '18 at 06:28

score 1 · Accepted Answer · answered Jun 22 '18 at 11:08

active is a server-side filter on Nmbrs.nl. It defaults to the value "active". Don't ask me why they choose to have an API reflect the user interface; it is weird, but it is the way it is.

To retrieve all employees from one or more companies (partitions), use:

use all

select * from employeesall

OR

select * from employeesinactive

These are recent additions to the Nmbrs.nl API tables supported.

Note that the output does NOT contain whether an employee is active. When you need that too, please use a view or:

 select 'active' type
 ,      t.*
 from   nmbrs..employeesactive t
 union all
 select 'inactive' type
 ,      t.*
 from   nmbrs..employeesinactive t

Retrieving inactive employees

1 Answers1