invantive-sqlinvantive-query-toolnmbrs

Retrieving inactive employees

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.

Solution

  • 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