Walbridge Export

Documentation for the Walbridge export script that generates an export file for Walbridge payroll system

Parameters

{mindate} - Start date for the export period
{maxdate} - End date for the export period

Data Components

The report consists of two main sections combined with a UNION:

Main Labor Hours Export:
- Employee ID and file key information
- Formatted work date (British date format: DD-MMM-YYYY)
- Job code (rightmost 10 characters of WG1.CODE)
- Phase code (from WG2)
- Category indicator (fixed as 'L' for Labor)
- Normal Working Hours (NWH) - Sum of pay designations 1, 8, 14, 15, 20, 23, 26, 29, 30, 32, 42
- Overtime (OT) - Sum of pay designations 2, 16, 17, 24, 27, 33, 36
- Double Overtime (DOT) - Sum of pay designations 3, 18, 19, 22, 25, 28, 34, 37
- Work company code (substring of WG1.Code)
- Trade and union codes
- Shift code
Other Hours Export:
- Same format as main export
- Specifically for "OTHER HOURS" column - pay designations 4, 5, 6, 7, 9, 11, 13, 29, 30, 31, 38, 41
- Includes the specific pay designation number as the "OTHR Type"
- Sort order '2' to appear after main export entries

Technical Implementation

The script uses:

Two separate SELECT statements combined with UNION
Subqueries to calculate sums of hours by category
String manipulation for date formatting (REPLACE + CONVERT)
String manipulation for job codes (RIGHT, Substring)
Explicit filtering by work group 1 (WG1 = 8)
Complex join conditions across multiple workgroup tables
Sort order column to control result sequence
Filtering to include only entries with non-zero hours

Output Format

The export includes a comprehensive set of columns:

Employee identifiers
Job and phase codes
Date information in British format
Hours categorized by type (NWH, OT, DOT, OTHR)
Numerically coded workgroup information
Trade, union, and shift codes
Empty placeholder columns for additional fields

T-SQL

walbridge_export.sql

select E.FILEKEY,
        E.idnum,
        REPLACE(CONVERT(VARCHAR(11), pd.eventdate, 106), ' ', '-') As Date,
        RIGHT(W1.CODE,10) as 'Job',
        W2.CODE as 'Phase',
        'L' as 'Cat.',
        (SELECT isnull(SUM(HOURS),0)
        FROM PAYDESHIST
        where PAYDESNUM in (1,8,14,15,20,23,26,29,30,32,42) and EVENTDATE=PD.EVENTDATE and FILEKEY=E.FILEKEY AND WG1 = W1.WGNUM AND WG2 = W2.WGNUM AND WG3 = W3.WGNUM AND WG4=W4.WGNUM AND WG5=W5.WGNUM)As NWH,
        (SELECT isnull(SUM(HOURS),0)
        FROM PAYDESHIST
        where PAYDESNUM in (2,16,17,24,27,33,36) and EVENTDATE=PD.EVENTDATE and FILEKEY=E.FILEKEY AND WG1 = W1.WGNUM AND WG2 = W2.WGNUM AND WG3 = W3.WGNUM AND WG4=W4.WGNUM AND WG5=W5.WGNUM)As OT,
        (SELECT isnull(SUM(HOURS),0)
        FROM PAYDESHIST
        where PAYDESNUM in (3,18,19,22,25,28,34,37) and EVENTDATE=PD.EVENTDATE and FILEKEY=E.FILEKEY AND WG1 = W1.WGNUM AND WG2 = W2.WGNUM AND WG3 = W3.WGNUM AND WG4=W4.WGNUM AND WG5=W5.WGNUM)As DOT,
        '' As OTHR,
        ''  as 'OTHR Type',
        Substring(W1.Code,4,2) as 'Work Company',
        '' as 'TSH Dept',
        '' as 'TSH GL',
        '' as 'TSH WL',
        '' as 'TSH EQP',
        '' as 'TSH Doc',
        W4.Code as 'TSH Trade',
        W5.Code as 'TSH Union',
        '' as 'TSH OH Rate',
        '' as 'TSH Check',
        W3.Code as 'TSH Shift',
        '' as 'TSH NH Rate',
        '' as 'TSH OT Rate',
        '' as 'TSH DT RATE',
        '' as 'TSH OTHR Rate',
        '' as 'Expense Code',
        '' as 'Expense Quantity',
        '1' as Sort
    from EMPLOYEES E, PAYDESHIST PD, WORKGROUP1 W1, WORKGROUP2 W2, WORKGROUP3 W3, WORKGROUP4 W4, WORKGROUP5 W5
    where (E.FILEKEY=PD.FILEKEY)
        and (PD.WG1=W1.WGNUM)
        and (PD.WG2=W2.WGNUM)
        and (PD.WG3=W3.WGNUM)
        and (PD.WG4=W4.WGNUM)
        and (PD.WG5=W5.WGNUM)
        and ((PD.EVENTDATE>={mindate}) and (PD.EVENTDATE<={maxdate}))
        and (SELECT isnull(SUM(HOURS),0)
        FROM PAYDESHIST
        where PAYDESNUM in (1,7,8,14,15,20,23,26,29,30,31,32,38,41,42,2,16,17,31,24,27,33,36,3,18,19,22,25,28,34,37) and EVENTDATE=PD.EVENTDATE and FILEKEY=E.FILEKEY AND WG1 = W1.WGNUM AND WG2 = W2.WGNUM AND WG3 = W3.WGNUM AND WG4=W4.WGNUM AND WG5=W5.WGNUM) >0
        /*Set WG1 */
        and e.wg1 = 8
    GROUP BY E.FILEKEY, E.IDNUM, PD.EVENTDATE, W1.CODE, W2.CODE, W3.CODE, W4.CODE, W5.CODE, W1.WGNUM,W2.WGNUM,W3.WGNUM,W4.WGNUM,W5.WGNUM

UNION
    /*Adds rows for OTHER HOURS column--------------------------------------------*/
    select E.FILEKEY, E.idnum,
        REPLACE(CONVERT(VARCHAR(11), pd.eventdate, 106), ' ', '-') As Date,
        RIGHT(W1.CODE,10) as 'Job',
        W2.CODE as 'Phase',
        'L' as 'Cat.', '' As NWH, '' As OT, '' As DOT,
        PD.HOURS as OTHR,
        PD.PAYDESNUM as 'OTHR Type',
        Substring(W1.Code,4,2) as 'Work Company',
        '' as 'TSH Dept', '' as 'TSH GL', '' as 'TSH WL', '' as 'TSH EQP', '' as 'TSH Doc', W4.Code as 'TSH Trade', W5.Code as 'TSH Union', '' as 'TSH OH Rate', '' as 'TSH Check',
        W3.Code as 'TSH Shift', '' as 'TSH NH Rate', '' as 'TSH OT Rate', '' as 'TSH DT RATE', '' as 'TSH OTHR Rate', '' as 'Expense Code', '' as 'Expense Quantity',
        '2' as Sort
    from EMPLOYEES E, PAYDESHIST PD, WORKGROUP1 W1, WORKGROUP2 W2, WORKGROUP3 W3, WORKGROUP4 W4, WORKGROUP5 W5
    where (E.FILEKEY=PD.FILEKEY)
        and (PD.WG1=W1.WGNUM) and (PD.WG2=W2.WGNUM) and (PD.WG3=W3.WGNUM)and (PD.WG4=W4.WGNUM)and (PD.WG5=W5.WGNUM)
        and ((PD.EVENTDATE>={mindate}) and (PD.EVENTDATE<={maxdate}))
        /*Set This PayDesNum*/
        AND PD.PAYDESNUM in (4,5,6,7,9,11,13,29,30,31,38,41)
        and (SELECT isnull(SUM(HOURS),0)
        FROM PAYDESHIST
        where PAYDESNUM in (4,5,6,7,9,11,13,29,30,31,38,41) /*<---*/ and EVENTDATE=PD.EVENTDATE and FILEKEY=E.FILEKEY AND WG1 = W1.WGNUM AND WG2 = W2.WGNUM AND WG3 = W3.WGNUM AND WG4=W4.WGNUM AND WG5=W5.WGNUM) >0
        /*Set WG1 */
        and e.wg1 = 8
    GROUP BY E.FILEKEY, E.IDNUM, PD.EVENTDATE,PD.HOURS, PD.PAYDESNUM, W1.CODE, W2.CODE, W3.CODE, W4.CODE, W5.CODE, W1.WGNUM,W2.WGNUM,W3.WGNUM,W4.WGNUM,W5.WGNUM

order by 2,3 DESC,29

Content Inventory

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