Invoice Export with Transactions

This report generates detailed invoice data for agricultural operations, including transaction timing information. It aggregates work activities by date, location, crew, and activity type, providing comprehensive billing information with start/end times for each work session.

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 1 main data aggregation section:

Work Activity Summary - Aggregates regular work hours (paydesnum 1) with transaction timing details

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 (Workgroup 5)
Grower	Grower/client name (Workgroup 1)
Location	Work location (Workgroup 2)
Crew	Crew assignment (Workgroup 3)
StartT	Work session start time
EndT	Work session end time
ShiftHours	Individual shift hours
Workers	Number of workers in the session
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
WORKGROUPTRANS table for timing details
Aggregation functions (COUNT, SUM) for totals
Complex WHERE clause filtering for data integrity

Notes

Only processes regular work hours (paydesnum = 1)
Excludes test employee (filekey != 1)
Requires workgroup 7 = 1 for inclusion
Only includes records with hours > 0
Transaction timing requires matching records in WORKGROUPTRANS
Commented section available for crew leader drive time (paydesnum = 8)
Results sorted by record type, date, activity, and crew
Provides more detailed timing than standard invoice export

T-SQL

invoice_export_w_transactions.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),StartT Datetime, EndT Datetime, 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,wt.STARTTIME,wt.ENDTIME, 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, WORKGROUPTRANS wt

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 AND--Test Employee
wt.EVENTDATE=pd.EVENTDATE AND
wt.FILEKEY=pd.FILEKEY AND
wt.WG1=pd.WG1 AND
wt.WG2=pd.WG2 AND
wt.WG3=pd.WG3 AND
wt.WG5=pd.WG5

Group By pd.EVENTDATE, w1.NAME, w2.NAME, w3.NAME, w5.NAME, pd.HOURS, pd.PAYDESNUM, wt.STARTTIME, wt.ENDTIME 
/*
--==========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,wt.STARTTIME,wt.ENDTIME, pd.HOURS, COUNT(pd.filekey) as SUMOFWORKERS, Sum(pd.HOURS) as SUMOFTTLHOURS, Sum(PD.Dollars)

from PAYDESHIST pd, WORKGROUP1 w1, WORKGROUP2 w2, WORKGROUP3 w3, WORKGROUPTRANS wt

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
PD.Hours>0 AND
PD.wg7=1 AND
pd.filekey!=1 AND --Test Employee
wt.EVENTDATE=pd.EVENTDATE AND
wt.FILEKEY=pd.FILEKEY AND
wt.WG1=pd.WG1 AND
wt.WG2=pd.WG2 AND
wt.WG3=pd.WG3

Group By pd.EVENTDATE, w1.NAME, w2.NAME, w3.NAME, pd.HOURS, pd.PAYDESNUM, wt.STARTTIME, wt.ENDTIME
*/
--=========Display Results
Select * from @MANZANA

order by 1,2,3,6 desc

Content Inventory

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