How to select Employee Pay Rates from SQL
Clarity Database Selection – Accessing Employee Pay Rates
Disclosure:
This example is provided “as is” and for the intention of data selection only. Any updating, deleting, or inserting of records using this, or other similar queries, will not fall under the support agreement with Paymate Software. Furthermore, any help required on using or producing these queries must be done by booking a session with a service agent and is also subject to the standard consulting billing rates of Paymate.
Notes:
The Clarity Payroll software runs on a SQL Database engine and the table that contains the employee default rates of pay are year sensitive. Therefore, when creating your queries you should make sure that you have a year parameter to ensure the selection of the correct data.
Tables:
There are three tables that you will require to obtain the rates of pay for an employee. They are as follows:
- n Employees – This table contains the employee master information
- n Payroll_Categories – This table contains a list of all the payroll categories defined in the application. Both user defined and system categories are in this table.
- n Employees_master_Paycard – This table contains the employees payroll defaults. However, categories that are “Overtime” type will not contain the rate of pay in this table. This is due to the fact that the overtime rate is calculated when payroll is processed and NOT saved to history. Furthermore, this table is the aforementioned table that is YEAR sensitive!
Database Diagram:
The following diagram will show how the tables will link together.
SQL Query:
The following is the SQL Query that would be produced from the above table selection example. Take note of the where conditions at the end of the query. The Year should be a parameter for your query as you are designing it, either obtain this from the current calendar year or entered by the user.
The pc.Number parameter is the list of payroll categories that you want included in the report. These are the payroll category numbers as defined within the payroll Software of Clarity under SetupàPayroll SettingsàPayroll Categories. Paymate always recommends the use of the Category number for easy reference by the user.
select e.Number, e.Surname, e.G_Name, e.M_Name, pc.Number, pc.Description, emp.Year, emp.Unit, emp.Rate, emp.Amount, emp.Percentage, emp.Minimum, emp.Maximum from employees e inner join employees_master_Paycard emp on emp.id_employee = e.id inner join payroll_Categories pc on pc.id = emp.id_payroll_Category where emp.Year = 2011 and pc.Number in ('102') Order by e.Number, pc.Number |
Other Parameters:
You may choose to use other parameters in the query selection. For example the Payroll_Categories.KIND field will tell you if the category is an Earning (E); Benefit (B); Tax (T); Payroll Tax (P); Deduction (D); Accrual (A); or Balance category for the accrual (L). Categories of Kind Other (O) are system categories and should NEVER be deleted, modified, or duplicated otherwise you risk causing errors in the software.
The Payroll_Categories.TYPE field lets you know how the category is managed within the application. This will be types Regular (RG); Hourly (HR); Overtime (OV); Piecework (PC); or Script (SA). Remember that categories of type Overtime do not have the hourly rate of pay assigned to them in the master paycards, as these are calculated during the processing of payroll. Furthermore, rates and values for scripted payroll categories are also managed during the processing of payroll and anything found for those categories in the employees master paycard should be looked at suspiciously.
