Daily TimeBank Export

Documentation for the Daily TimeBank export script that generates an export file for TimeBank system

Parameters

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

Data Components

The report extracts and organizes the following data:

Employee Information:
- FILEKEY (internal ID)
- Full name (Last, First Middle)
- Employee ID number
- SSN
- Active status
Workgroup Assignments:
- WG1-WG7 codes and names for employee's regular assignment
- WG1-WG7 codes and names for the specific worked entries
Pay Information:
- Pay class name and number
- Pay designation number and name
- Hours and dollar amounts
- Effective rates
- Pay designation multiplier
Date Information:
- Period start and end dates
- Day worked date
- Week end date
- Week number (1 or 2 within the period)
Custom Fields:
- HR custom fields 1-6
- Pay parameter class name

Technical Implementation

The script uses:

Multiple table joins to combine employee, pay history, workgroup, and HR data
CASE statements to calculate week end dates and week numbers
Explicit casting to ensure consistent datetime formats
Filtering to exclude specific workgroup combinations
Ordering by employee name and date

T-SQL

daily_timebank_export.sql

select
    E.FILEKEY,
    E.LASTNAME+', '+E.FIRSTNAME+' '+E.INITIAL,
    E.IDNUM,
    E.SSN,
    E.WG1, E.WG2, E.WG3, E.WG4, E.WG5, '0' as wg6, '0' as wg7,

    W1E.CODE, W2E.CODE, W3E.CODE, W4E.CODE, W5E.CODE, '0' as wg6code, '0' as wg7code,

    W1E.NAME, W2E.NAME, W3E.NAME, W4E.NAME, W5E.NAME, '' as wg6name, '' as wg7name,

    E.PAYCLASS,
    PC.NAME,
    pd.PAYDESNUM,
    pds.NAME,
    pd.hours,
    pd.dollars,
    case PDS.ISHOURS when 0 then PD.DOLLARS else PD.HOURS end as amount,

    PD.WG1, pd.wg2, pd.wg3, pd.wg4, pd.wg5, '0' as workedpdwg6, '0' as workedpdwg7,

    pdW1.CODE, pdW2.CODE, pdW3.CODE, pdW4.CODE, pdW5.CODE, '0' as wg6code, '0' as wg7code,

    pdW1.NAME, pdW2.NAME, pdW3.NAME, pdW4.NAME, pdW5.NAME, '' as wg6code, '' as wg7code,

    Cast({mindate} as datetime) as periodstart,
    Cast({maxdate} as datetime) as periodend,
    CAST(pd.eventdate as datetime) as DayWorkedDate,
    Case when pd.eventdate <= DATEADD(dd, 8-(DATEPART(dw, {mindate})), {mindate})
		then DATEADD(dd, 8-(DATEPART(dw, {mindate})), {mindate})
		else {maxdate} end as WeekEndDate,
    Case when pd.eventdate <= DATEADD(dd, 8-(DATEPART(dw, {mindate})), {mindate})
		then 1
		else 2 end as WeekNumber,
    E.ACTIVESTATUS,
    HR.CUSTOM1, HR.CUSTOM2, HR.CUSTOM3, HR.CUSTOM4, HR.CUSTOM5, HR.CUSTOM6,
    PAR.CLASSNAME,
    E.RATE,
    pd.RATE as EffectiveRate,
    pds.RATE as PayDesMultiplyer

from
    PAYDESHIST PD, PAYDESIGNATIONS PDS, EMPLOYEES E,
    WORKGROUP1 W1E, WORKGROUP2 W2E, WORKGROUP3 W3E, WORKGROUP4 W4E, WORKGROUP5 W5E,
    WORKGROUP1 pdW1, WORKGROUP2 pdW2, WORKGROUP3 pdW3, WORKGROUP4 pdW4, WORKGROUP5 pdW5,
    PAYCLASSES PC, EMPLOYEEHR HR, PARAMETERS PAR

where 
	E.FILEKEY=pd.FILEKEY
    and E.WG1=W1E.WGNUM and E.WG2=W2E.WGNUM and E.WG3=W3E.WGNUM and E.WG4=W4E.WGNUM and E.WG5=W5E.WGNUM
    and pd.wg1=pdw1.WGNUM and pd.wg2=pdW2.WGNUM and pd.WG3=pdW3.WGNUM and pd.WG4=pdW4.WGNUM and pd.WG5=pdw5.WGNUM
    and pd.PAYDESNUM=pds.PAYDESNUM
    and E.PAYCLASS=PC.PAYCLASSNUM
    and E.FILEKEY=HR.OWNERID
    and E.PAYCLASS=PC.PAYCLASSNUM
    and PC.PAYPERCLASS=PAR.CLASSNUM
    and PAR.CLASSTYPE=6
    and PD.EVENTDATE>={mindate} and PD.EVENTDATE<={maxdate}
    and e.wg2 not in (8217,8218,8221,099)
    and e.wg3 not in (099,2101,2102,2103,2104,2105,2106,2107,2108,2114,2115,2116,2124,2127,2187,2188,2189)

order by 2, pd.EVENTDATE

Content Inventory

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

Daily TimeBank Export

Parameters

Data Components

Technical Implementation

T-SQL

Content Inventory

On this page