Voucher Report v1

This comprehensive report generates a detailed voucher-style payroll report with employee pay details, subtotals, and summary sections. It provides a complete breakdown of hourly and salary employees with configurable pay classes and designations, formatted for official payroll documentation.

Parameters

@TimeLocation - Time location filter (example: '1')
@MIN - Start date for the pay period (example: '2025-03-01')
@MAX - End date for the pay period (example: '2025-03-31')
{userid} - User ID for access control (parameter substitution)

Data Components

The report consists of 7 main sections:

Header Information - County installation details
Employee Details - Individual employee pay records
Pay Designation Details - Detailed pay breakdown by type
Employee Subtotals - Individual employee totals
Hourly Employee Totals - Summary for hourly staff
Salary Employee Totals - Summary for salary staff
Report Grand Totals - Overall report summary

Example Output

Voucher Report Example

💡 Click the image above to zoom in and see the details more clearly!

The report generates a comprehensive voucher-style payroll document with employee details, pay breakdowns, subtotals, and summary sections for both hourly and salary employees.

Technical Implementation

The script uses:

Multiple table variables for pay class and designation configuration
Complex CASE statements for secondary salary distribution
User access control integration
Conditional rate and dollar removal for salary employees
Hierarchical subtotal calculations
Dynamic section headers and formatting

Configuration Tables

Salary Pay Classes: 1, 3, 5, 6 Hourly Pay Classes: 2, 4
Salary Rate Designations: 2, 16, 19, 20, 26, 98 Exported Pay Designations: 1-26, 98 (excluding 14, 17)

Notes

Includes user access control filtering
Excludes test employee (IDNUM != '999999999')
Handles secondary salary distributions from custom fields
Removes rates/dollars for salary employees (except specific designations)
Removes rates/dollars for compensatory time (paydesnum 21)
Provides separate totals for hourly and salary employees
Includes county installation header information
Results ordered by section and employee for structured output
Supports configurable pay class and designation filtering

T-SQL

voucher_report_v1_.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE
SET @MIN={mindate}
SET @MAX={maxdate}

-----------------------------Report Parameters
DECLARE @County VARCHAR(100) = 'JAY COUNTY'
DECLARE @TimeLocation NVARCHAR(MAX) = '2'
DECLARE @SalPayclass NVARCHAR(MAX) = '1,2,11,12'
DECLARE	@HourlyPayclass NVARCHAR(MAX) = '3,4,5,6,7,8,9,10'
-- Paydesignation that will show a rate for Salary
DECLARE	@SalRates NVARCHAR(MAX) = '2,16,19,20,26,34,98'
DECLARE	@Paydes NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,11,15,16,17,18,19,20,21,22,23,24,25,26,34,98'

--Create Voucher table
DECLARE @VOUCHER TABLE(Filekey bigint, Payclass int, LN Varchar(30), LastName varchar(30), FirstName varchar(30), IDNUM varchar(20), TimeLOCName VARCHAR(30), MainDis varchar(40), Event varchar(50), Paydesnum smallint, paydesname varchar(10), Hours REAL, Rate real, dollars real, GrossPay real)
---------Insert County Info
Insert into @VOUCHER Select 0,null,null,null,null,null,'******  INSTALLED IN',@County,CONVERT(varchar,DATEPART(Year,Getdate())) + ' ******',null,null,null,null,null,null
Insert into @VOUCHER Select 0,null,null,null,null,null,null,null,null,null,null,null,null,null,null
Insert into @VOUCHER Select 0,null,null,null,null,null,null,null,null,null,null,null,null,null,null

---------Insert EMP info
INSERT INTO @VOUCHER SELECT e.FILEKEY,e.PAYCLASS,e.lastname,e.lastname,e.FIRSTNAME,e.IDNUM,null,ehr.Custom2,null,null,null,null,null,null,null

from employees e, EMPLOYEEHR ehr
where e.FILEKEY = ehr.OWNERID
and e.ACTIVESTATUS=0
and e.IDNUM != '999999999'
and (select count(*) from PAYDESHIST where FILEKEY=e.FILEKEY and EVENTDATE between @MIN and @MAX and WG5=@TimeLocation)>0

--MY EMPLOYEES
AND
(((SELECT COUNT(*) FROM USERACCESS U WHERE (U.USERID={userid}) AND (((U.WG1=E.WG1) OR (U.WG1=0)) AND ((U.WG2=E.WG2) OR (U.WG2=0)) AND ((U.WG3=E.WG3) OR (U.WG3=0)) AND ((U.WG4=E.WG4) OR (U.WG4=0)) AND ((U.WG5=E.WG5) OR (U.WG5=0)) AND ((U.WG6=E.WG6) OR (U.WG6=0)) AND ((U.WG7=E.WG7) OR (U.WG7=0))))>0))

--------Get Paydes Hours
INSERT INTO @voucher select e.FILEKEY,e.PAYCLASS,e.LASTNAME,null,null,null,wg5.name,
CASE
	When pd.paydesnum = 98 and pd.WG6 != e.WG6 and pd.WG6 = (Select Convert(Int,LEFT(CUSTOM5,2)) from EMPLOYEEHR where OWNERID=E.FILEKEY)
		then (Select RIGHT(CUSTOM5,19) from EMPLOYEEHR where OWNERID=E.FILEKEY)
    When pd.paydesnum = 98 and pd.WG6 != e.WG6 and pd.WG6 = (Select Convert(Int,LEFT(CUSTOM6,2)) from EMPLOYEEHR where OWNERID=E.FILEKEY)
		then (Select RIGHT(CUSTOM6,19) from EMPLOYEEHR where OWNERID=E.FILEKEY)
	ELSE null
end,wg7.NAME,pd.PAYDESNUM,pds.NAME,ISNULL(sum(PD.HOURS),0),PD.RATE,Round(isnull(sum(PD.DOLLARS),0),2),null

from employees e, PAYDESHIST pd, PAYDESIGNATIONS pds,  WORKGROUP5 wg5, WORKGROUP7 WG7
where e.filekey = pd.FILEKEY
and pd.WG5 = @TimeLocation
and wg5.WGNUM = pd.WG5
and wg7.WGNUM = pd.WG7
and pds.PAYDESNUM = pd.PAYDESNUM
and pd.PAYDESNUM in (Select TRY_CAST(value AS INT) AS IntValue FROM STRING_SPLIT(@Paydes, ',') )
and pd.EVENTDATE between @MIN and @MAX
and e.ACTIVESTATUS=0
and e.IDNUM != '999999999'

--MY EMPLOYEES
AND
(((SELECT COUNT(*) FROM USERACCESS U WHERE (U.USERID={userid}) AND (((U.WG1=E.WG1) OR (U.WG1=0)) AND ((U.WG2=E.WG2) OR (U.WG2=0)) AND ((U.WG3=E.WG3) OR (U.WG3=0)) AND ((U.WG4=E.WG4) OR (U.WG4=0)) AND ((U.WG5=E.WG5) OR (U.WG5=0)) AND ((U.WG6=E.WG6) OR (U.WG6=0)) AND ((U.WG7=E.WG7) OR (U.WG7=0))))>0))

group by e.FILEKEY, e.PAYCLASS, e.lastname, wg5.name, wg7.name, pds.PAYDESNUM, pds.NAME, pd.RATE, pd.WG6, e.WG6, pd.PAYDESNUM

------------------Remove Salary Rates and Dollars
Update @VOUCHER Set Rate=null, dollars=null where Payclass in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@SalPayclass, ',')) and Paydesnum not in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@SalRates, ','))

------------------Remove CompEarn Rates and Dollars
Update @VOUCHER Set Rate=null, dollars=null where Paydesnum IN (27,28)

--------------SubTotals
INSERT INTO @voucher select V.FILEKEY,max(V.payclass),max(v.LN),null,null,null,null,null,null,'1001','SubTotal',
Sum(v.Hours),null,null,Sum(dollars)
from @VOUCHER V
where v.Filekey not in (0)
Group by v.Filekey

----------------------------------Hourly Totals
INSERT INTO @voucher select 9999999997,null,'zzz',null,null,null,null,null,null,null,null,null,null,null,null
INSERT INTO @voucher select 9999999997,null,'zzz',null,null,null,null,null,'HOURLY TOTALS',null,null,null,null,null,null

INSERT INTO @voucher select 9999999997,null,'zzz',null,null,null,null,null,null,v.PAYDESNUM,max(v.paydesname),ISNULL(sum(v.HOURS),0),null,Round(isnull(sum(v.DOLLARS),0),2),null
from @VOUCHER v
where v.Paydesnum not in (1001) and v.Payclass in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@HourlyPayclass, ','))
group by v.PAYDESNUM

INSERT INTO @voucher select 9999999997,null,'zzz',null,null,null,null,null,null,'1001','SubTotal',null,null,null,null
Update @VOUCHER set Hours=(select sum(hours) from @VOUCHER where filekey = 9999999997 and Paydesnum Not IN (1001) ) where Filekey=9999999997 and Paydesnum = 1001
Update @VOUCHER set GrossPay=(select sum(dollars) from @VOUCHER where filekey=9999999997) where Filekey=9999999997 and Paydesnum = 1001

----------------------------------Salary Totals
INSERT INTO @voucher select 9999999998,null,'zzz',null,null,null,null,null,null,null,null,null,null,null,null
INSERT INTO @voucher select 9999999998,null,'zzz',null,null,null,null,null,'SALARY TOTALS',null,null,null,null,null,null

INSERT INTO @voucher select 9999999998,null,'zzz',null,null,null,null,null,null,v.PAYDESNUM,max(v.paydesname),ISNULL(sum(v.HOURS),0),null,Round(isnull(sum(v.DOLLARS),0),2),null
from @VOUCHER v
where v.Paydesnum not in (1001) and v.Payclass in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@SalPayclass, ','))
group by v.PAYDESNUM

INSERT INTO @voucher select 9999999998,null,'zzz',null,null,null,null,null,null,'1001','SubTotal',null,null,null,null
Update @VOUCHER set Hours=(select sum(hours) from @VOUCHER where filekey = 9999999998 and Paydesnum Not IN (1001) ) where Filekey=9999999998 and Paydesnum = 1001
Update @VOUCHER set GrossPay=(select sum(dollars) from @VOUCHER where filekey=9999999998) where Filekey=9999999998 and Paydesnum = 1001

----------------------------------Report Totals
INSERT INTO @voucher select 9999999999,null,'zzz',null,null,null,null,null,null,null,null,null,null,null,null
INSERT INTO @voucher select 9999999999,null,'zzz',null,null,null,null,null,'REPORT TOTALS',null,null,null,null,null,null

INSERT INTO @voucher select 9999999999,null,'zzz',null,null,null,null,null,null,v.PAYDESNUM,max(v.paydesname),ISNULL(sum(v.HOURS),0),null,Round(isnull(sum(v.DOLLARS),0),2),null
from @VOUCHER v
where v.Paydesnum not in (1001) and filekey not in (9999999997,9999999998)
group by v.PAYDESNUM

INSERT INTO @voucher select 9999999999,null,'zzz',null,null,null,null,null,null,'1001','SubTotal',null,null,null,null

Update @VOUCHER set Hours=(select sum(hours) from @VOUCHER where filekey = 9999999999 and Paydesnum Not IN (1001) ) where Filekey=9999999999 and Paydesnum = 1001
Update @VOUCHER set GrossPay=(select sum(GrossPay) from @VOUCHER where filekey in (9999999997,9999999998) and Paydesnum=1001 ) where Filekey=9999999999 and Paydesnum = 1001

----------------------------Report Totals Clean up
Delete from @VOUCHER where Filekey in (9999999997,9999999998) and ((Select Count(Filekey) from @VOUCHER where Payclass in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@HourlyPayclass, ',')))=0 or (Select Count(Filekey) from @VOUCHER where Payclass in (Select TRY_CAST(value AS INT) FROM STRING_SPLIT(@SalPayclass, ',')))=0 )

----------------------------Display Results
Select filekey, payclass, LN, Lastname, firstname, IDNUM, TimeLOCName, MainDis, Event, paydesnum, paydesname, Hours, Rate, dollars, GrossPay from @VOUCHER order by 3,1,10

Content Inventory

Doc File: content/docs/reports/pay_information/voucher_report_v1.mdx
SQL Script: SQL/reports/pay_information/voucher_report_v1_.sql
Screenshot: public/img/screenshots/reports/employee_information/VoucherReport.png

Voucher Report v1

On this page