God Lee Payroll Export

Parameters

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

Screenshot

Data Components

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

1. Primary Export Data:

   • Employee ID and name
   • GL Account from STARTYPES table
   • Pay and leave codes
   • Hours worked
   • Type indicator (fixed as 'H')
   • Pay rate
   • Dollar amount
   • Empty placeholders for start/end times and notes

2. Missing GL Code Data:

   • Same structure as primary data
   • Special handling for cases where GL codes are missing from the STARTYPES table
   • Uses 'GL CODE NOT IN EMP RATINGS' as the GL account text

Technical Implementation

The script uses:

• Two separate SELECT statements combined with UNION
• Joins between Employees, STARTYPES, and PAYDESHIST tables
• ISNULL function to handle potential NULL values in hour/dollar sums
• Association between STARTYPES.ABB and pay history WG5
• Special handling for cases where WG5 doesn't match any STARTYPES.ABB value
• Consistent formatting for employee names (lastname, firstname)
• Ordering by employee ID

T-SQL

Select

  e.idnum as 'EE#',
  e.lastname+', '+e.firstname as 'Name',
  st.Name as 'GL Account',
  pd.paydesnum as 'Pay Code',
  pd.paydesnum as 'Leave Code',
  isnull(Sum(pd.hours),0) as 'Hours',
  'H' as 'Type',
  pd.rate as 'Rate',
  '' as 'Start',
  '' as 'End',
  isnull(Sum(pd.dollars),0) as 'Amount',
  '' as 'Note'

From Employees e, STARTYPES st, PAYDESHIST pd

where
   e.filekey = pd.filekey
   and (st.ABB = pd.WG5) 
   and pd.EVENTDATE between {mindate} and {maxdate}

group by e.IDNUM, e.LASTNAME,e.FIRSTNAME, st.NAME, pd.PAYDESNUM, pd.RATE

UNION

Select

  e.idnum as 'EE#',
  e.lastname+', '+e.firstname as 'Name',
  'GL CODE NOT IN EMP RATINGS',
  pd.paydesnum as 'Pay Code',
  pd.paydesnum as 'Leave Code',
  isnull(Sum(pd.hours),0) as 'Hours',
  'H' as 'Type',
  pd.rate as 'Rate',
  '' as 'Start',
  '' as 'End',
  isnull(Sum(pd.dollars),0) as 'Amount',
  '' as 'Note'

From Employees e, STARTYPES st, PAYDESHIST pd

where
   e.filekey = pd.filekey
   and pd.WG5 not in (Select ABB from STARTYPES)
   and pd.EVENTDATE between {mindate} and {maxdate}

group by e.IDNUM, e.LASTNAME,e.FIRSTNAME, st.NAME, pd.PAYDESNUM, pd.RATE 

order by 1

Content Inventory

• Doc File: content/docs/payroll_exports/god_lee_payroll_export.mdx
• SQL Script: SQL/payroll_exports/god_lee_payroll_export.sql
• Screenshot: public/img/screenshots/reports/exports/LongGLcodes.png