LOW Payroll Export (Version 1)

Documentation for the LOW Payroll Export (Version 1) script that generates an export file for LOW payroll system

Download Alternative Formats

📥 Browser Export Format:

Download Export File - Download this export for external processing

Parameters

{mindate} - Start date for the export period (commented out in sample, example: '2025-03-01')
{maxdate} - End date for the export period (commented out in sample, example: '2025-03-31')
@SalPayclass - Table defining salary pay classes (configurable, default includes classes 1 and 2)

Data Components

The report organizes data into multiple sections based on attendance type:

Regular Hours:
- Regular time entries (pay designations 1, 3, 4, 5, 7, 8, 9, 10, 13, 17, 18, 20)
- Special event handling for events marked with an asterisk (*) in WG7
- Rate handling for salaried vs. hourly employees
Overtime Hours:
- Overtime entries (pay designations 2, 19)
- Special event handling similar to regular hours
- Always includes pay rates
Straight Overtime Hours:
- Straight overtime entries (pay designation 11)
- Similar handling to regular overtime
Absence Hours:
- Absence entries (pay designation 100)
- Special event handling
Comp Time Used Hours:
- Comp time entries (pay designation 100, overlaps with absence)
- Tagged as "Comp Time (Paid)"
Unpaid Hours:
- Unpaid time entries (pay designation 22)
- Follows same format as other categories
- Special rate handling for salaried employees

Output Format

The export generates columns in the following format:

Pay Record ID (Employee ID with work group suffix)
Attendance Type (Regular, Overtime, STR OT, Absence, Comp Time (Paid), Unpaid, Event)
Event Date (formatted as MM/dd/yyyy)
Absence Type
Hours
Work Location
Additional fields for pay groups, comments, descriptions
Special Rate (except for salaried employees)
Special Distribution

Technical Implementation

The script uses:

SET NOCOUNT ON and SET ANSI_WARNINGS OFF to control SQL Server behavior
Temporary tables to organize data
Multiple INSERT statements for different attendance types
CASE statements for event handling and type mapping
String manipulation for descriptions
Special handling for salaried employees (hiding rates)
Date formatting for consistent output

T-SQL

low_payroll_export_v1.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE,
		@MAX DATE

-------------------Define your salary payclasses after "Values"
DECLARE	@SalPayclass Table(Num smallint)
insert into @SalPayclass(Num) Values (1),(2) 

		
--SET @MIN={mindate}
--SET @MAX={maxdate}

SET @MIN='2025-03-01'
SET @MAX='2025-03-31'


DECLARE @LOW TABLE(PayRecordID VARCHAR(50), AttType VARCHAR(50), EventDate Date, Absence VARCHAR(50), HRS DECIMAL(38,2), WORKLOC VARCHAR(50), PAYGROUP VARCHAR(50), COMMENT VARCHAR(50), DESCRIPTION Varchar(25), RATEDIST Varchar(50), SpecialRate real, SpecialDIST varchar(50))

----------------------------REG HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'Regular'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,
Case
	When e.PAYCLASS IN (Select num from @SalPayclass) Then null
	ELSE pd.RATE
END,
null

FROM WORKGROUP5 WG5, WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (1,3,4,5,7,8,9,10,13,17,18,20)

----------------------------OT HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'Overtime'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,pd.RATE,null

FROM WORKGROUP5 WG5,  WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (2,19)

----------------------------Straight OT HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'STR OT'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,pd.RATE,null

FROM WORKGROUP5 WG5,  WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (11)		

----------------------------Absence HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'Absence'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,pd.RATE,null

FROM WORKGROUP5 WG5,  WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (100)	

----------------------------Comp Time Used HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'Comp Time (Paid)'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,pd.RATE,null

FROM WORKGROUP5 WG5,  WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (100)	

----------------------------UnPaid HOURS
INSERT INTO @LOW SELECT E.IDNUM+'-0'+Convert(varchar,pd.wg6),
CASE
	When WG7.name like'[*]%' then 'Event'
	ELSE 'Unpaid'
End,
pd.EVENTDATE,NULL,pd.HOURS,WG5.NAME,NULL,NULL,
CASE
	When WG7.name like'[*]%' then RIGHT(WG7.name, LEN(WG7.Name) - 1)
	ELSE NULL
End,
NULL,
Case
	When e.PAYCLASS IN (Select num from @SalPayclass) Then null
	ELSE pd.RATE
END,
null

FROM WORKGROUP5 WG5,  WORKGROUP7 WG7, EMPLOYEES E, PAYDESHIST PD 
WHERE e.FILEKEY=pd.FILEKEY 
	and pd.WG5=WG5.WGNUM
	and pd.WG7=wg7.WGNUM
	and pd.EVENTDATE between @MIN and @MAX
	and pd.PAYDESNUM in (22)	

----------------------------Display Results
Select PayRecordID, AttType, format(EventDate,'MM/dd/yyyy'), Absence, Hrs,WORKLOC, PAYGROUP, COMMENT, DESCRIPTION, RATEDIST, SpecialRate, SpecialDIST from @LOW order by 1,3

Content Inventory

Doc File: content/docs/payroll_exports/low_payroll_export_v1.mdx
SQL Script: SQL/payroll_exports/low_payroll_export_v1.sql