PayrollReportByJobCode
Payroll Report broken down by jobcode
Table Specific Information
Select
Query the PayrollReportByJobCode table to retrieve a payroll report, broken down by jobcode, with filters to narrow down the results. The add-in will use the TSheets API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the add-in. By default the report will include results for a time range within the last 30 days. You can filter by StartDate and EndDate filters to get a payroll report over a different time range. The date range defined by StartDate and EndDate must not exceed 31 days.
- GroupId field supports the '=' and IN operators.
- StartDate, EndDate and IncludeAdvancedOvertime fields support the '=' operator.
For example, the following queries are processed server side:
SELECT * FROM PayrollReportByJobCode WHERE GroupId IN ('29474', '29474') AND IncludeAdvancedOvertime = false
SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'
Based on the columns selected in projection explicitly, the aggregated data displayed may change.
Aggregate payroll metrics broken down by jobcode id:
SELECT * FROM PayrollReport WHERE StartDate = '2019-11-01' AND EndDate = '2019-12-31'
Aggregate payroll metrics broken down by jobcode id per each user:
SELECT UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode
Aggregate payroll metrics broken down by jobcode id and date per each user:
SELECT Date, UserId, JobCodeId, TotalWorkSeconds, TotalReSeconds, TotalPtoSeconds, OvertimeSeconds FROM PayrollReportByJobCode
Columns
| Name | Type | Description |
| UserId | Int | Id of the users these totals are calculated for. |
| JobcodeId | Int | Id of the jobcode the metrics apply to. |
| StartDate | Date | Start of reporting timeframe. |
| EndDate | Date | End of the payroll reporting timeframe. |
| Date | String | Date that these total are calculated for. |
| TotalReSeconds | Int | Total time for the current shift, in seconds. |
| TotalOTSeconds | Int | Overtime, in seconds. |
| TotalDTSeconds | Int | Doubletime, in seconds. |
| TotalPTOSeconds | Int | Total PTO time, in seconds. |
| TotalWorkSeconds | Int | Total overall time, in seconds. |
| OvertimeSeconds | String | The overtime seconds object, where the key is the multiplier rate and the value is the number of seconds of overtime at that rate |
| FixedRateSeconds | String | The fixed rate seconds object, where the key is the multiplier rate and the value is the number of seconds of overtime at that rate |
Pseudo-Columns
Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| GroupId | String | A comma-seperated list of group ids. Only time for users from these groups will be included. |
| IncludeAdvancedOvertime | Boolean | If true, overtime will be formatted such that it includes the time for individual multipliers and can support more than just 1.5x (ot) and 2x (dt) overtime. |