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 cmdlet 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 cmdlet. 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. |