Invoice Export

This report generates standard invoice data for agricultural operations, aggregating work activities by date, location, crew, and activity type. It provides essential billing information including both regular work hours and crew leader drive time.

Parameters

@MIN - Start date for the export period (example: '9/23/2022')
@MAX - End date for the export period (example: '9/29/2022')

Data Components

The report consists of 2 main data aggregation sections:

Regular Work Hours - Standard work activities (paydesnum 1)
Drive Time - Crew leader drive time (paydesnum 8)

Output Format

The report returns invoice data with the following structure:

Column	Description
Record	Record type identifier (always 1)
Eventdate	Formatted work date
Activity	Work activity name or "Crew Leader" for drive time
Grower	Grower/client name (Workgroup 1)
Location	Work location (Workgroup 2)
Crew	Crew assignment (Workgroup 3)
ShiftHours	Individual shift hours
Workers	Number of workers in the activity
SumHours	Total hours for the activity
SumDollars	Total dollar amount for the activity

Technical Implementation

The script uses:

Table variable (@MANZANA) for data staging
FORMAT function for date presentation
Multiple workgroup JOINs for descriptive information
UNION structure combining regular work and drive time
Aggregation functions (COUNT, SUM) for totals
Complex WHERE clause filtering for data integrity

Notes

Processes regular work hours (paydesnum = 1) and drive time (paydesnum = 8)
Excludes test employee (filekey != 1)
Requires workgroup 7 = 1 for inclusion
Only includes records with hours > 0
Drive time records labeled as "Crew Leader" activity
Results sorted by record type, date, activity, and crew (descending)
Simpler format than transaction-detailed version
Standard format for most billing operations

T-SQL

invoice_export.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @MIN DATE, @MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='9/23/2022'
SET @MAX='9/29/2022'


DECLARE @MANZANA TABLE(Record int, Eventdate VARCHAR(50), Activity VARCHAR(50), Grower VARCHAR(50), Location VARCHAR(50), Crew VARCHAR(50), ShiftHours DECIMAL(38,2), Workers int, SumHours DECIMAL(38,2), SumDollars DECIMAL(38,2))

INSERT INTO @MANZANA Select 1, format(pd.EVENTDATE,'D') as DATE, w5.NAME as Activity, w1.NAME as Grower, w2.NAME as Location, w3.NAME as Crew, pd.HOURS, COUNT(pd.filekey) as SUMOFWORKERS, Sum(pd.HOURS) as SUMOFTTLHOURS, Sum(PD.Dollars) 

from PAYDESHIST pd, WORKGROUP1 w1, WORKGROUP2 w2, WORKGROUP3 w3, WORKGROUP5 w5

Where
pd.EVENTDATE between @MIN and @MAX AND
pd.PAYDESNUM IN (1) AND
w1.WGNUM=pd.WG1 AND
W2.WGNUM=pd.WG2 AND
w3.WGNUM=pd.WG3 AND
w5.WGNUM=pd.WG5 AND
PD.Hours>0 AND
PD.wg7=1 AND
pd.filekey!=1 --Test Employee

Group By pd.EVENTDATE, w1.NAME, w2.NAME, w3.NAME, w5.NAME, pd.HOURS, pd.PAYDESNUM 

--==========Drive Time
INSERT INTO @MANZANA Select 1, format(pd.EVENTDATE,'D') as DATE, 'Crew Leader', w1.NAME as Grower, w2.NAME as Location, w3.NAME as Crew, pd.HOURS, COUNT(pd.filekey) as SUMOFWORKERS, Sum(pd.HOURS) as SUMOFTTLHOURS, Sum(PD.Dollars) 

from PAYDESHIST pd, WORKGROUP1 w1, WORKGROUP2 w2, WORKGROUP3 w3, WORKGROUP5 w5

Where
pd.EVENTDATE between @MIN and @MAX AND
pd.PAYDESNUM IN (8) AND
w1.WGNUM=pd.WG1 AND
W2.WGNUM=pd.WG2 AND
w3.WGNUM=pd.WG3 AND
w5.WGNUM=pd.WG5 AND
PD.Hours>0 AND
PD.wg7=1 AND
pd.filekey!=1 --Test Employee

Group By pd.EVENTDATE, w1.NAME, w2.NAME, w3.NAME, w5.NAME, pd.HOURS, pd.PAYDESNUM 

--=========Display Results
Select * from @MANZANA

order by 1,2,3,6 desc

Content Inventory

Doc File: content/docs/reports/pay_information/invoice_export.mdx
SQL Script: SQL/reports/pay_information/invoice_export.sql