Schoolcraft Export

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

Parameters

{mindate} - Start date for the export period (commented out in sample, example: '2/15/2021')
{maxdate} - End date for the export period (commented out in sample, example: '2/28/2021')

Data Components

The report extracts the following data elements:

Report Identifiers:
- 'BW' (Biweekly) prefix
- Formatted end date (MM/dd/yyyy)
- Employee ID
Accounting Codes:
- Fund (WG1 code)
- Budget (WG2 code)
- Object Code (WG3 code)
- Position (WG4 code)
Time Information:
- Punch date (formatted as MM/dd/yyyy)
- Pay description (mapped via CASE statement)
- Total hours (aggregated by pay description)

Pay Description Mapping

The script maps workgroup and pay designation combinations to specific pay codes:

Regular Time Codes:
- RETR - Regular time for retirement-eligible positions
- FFNE - Full-time non-exempt
- FSFT - Full-time food service
- FSPT - Part-time food service
- CLFT - Clerical full-time
- CLPT - Clerical part-time
- OCFT - Office full-time
- OCPT - Office part-time
- PPFT - Professional full-time
- PPPT - Professional part-time
Overtime Codes:
- FSOT - Food service overtime
- CLOT - Clerical overtime
- OCOT - Office overtime
- PPOT - Professional overtime
- OVTM - General overtime
Special Codes:
- COMP - Comp time
- CWST - Custodial staff
- STUD - Student
- SPRM - Special premium
- STDP - Special department
- EFML - Emergency family leave
- EPSL - Emergency paid sick leave

Technical Implementation

The script uses:

Common Table Expression (CTE) to organize data
CASE statements for pay description mapping
FORMAT function for consistent date formatting
Filtering to exclude specific workgroups (8217, 8218, etc.)
Filtering to exclude specific pay designations (14, 22, etc.)
Explicit type handling with CAST/FORMAT
Grouping to aggregate hours by pay description
Ordering by employee ID and punch date

Download Alternative Formats

📥 Browser Export Format: This need updating

Download Export File - Download this export for external processing

T-SQL

schoolcraft_export.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE
SET @MIN={mindate}
SET @MAX={maxdate}

select ex.BW, ex.MD, ex.ID, ex.Fund, ex.Budget, ex.ObjectCode, ex.Position, ex.PunchDate, ex.PayDesc, sum(ex.HRS) as TotHrs from

(Select 'BW' as BW, Format(@MAX, 'MM/dd/yyyy') as MD, e.idnum as ID, w1.code as Fund, w2.code as Budget, w3.code as ObjectCode, w4.code as Position
,format(pd.EVENTDATE, 'MM/dd/yyyy') as PunchDate, 
Case
       When w3.CODE in (2110) and pd.PAYDESNUM in (1,4,5,6,7,17,24,29) then 'RETR'
       When w3.CODE in (2112,2124,2138) and pd.PAYDESNUM in (1,4,5,6,7,9,10,11,15,17,29) then 'FFNE'
       When w3.CODE in (2155) and pd.PAYDESNUM in (1,4,5,6,7,9,17,29) then 'FSFT'
       When w3.CODE in (2155,2156) and pd.PAYDESNUM in (2) then 'FSOT'
       When w3.CODE in (2156) and pd.PAYDESNUM in (1,4,5,6,7,9,10,17,24,29) then 'FSPT'
       When w3.CODE in (2157) and pd.PAYDESNUM in (8) then 'FSSE'
       When w3.CODE in (2136) and pd.PAYDESNUM in (1,4,5,6,7,9,10,11,15,16,17,29) then 'FTTP'
       When w3.CODE in (2118,2119,2150) and pd.PAYDESNUM in (1,4,5,6,7,9,10,11,15,16,17,29) then 'CLFT'
       When w3.CODE in (2175) and pd.PAYDESNUM in (1) then 'CL17'
       When w3.CODE in (2118,2119,2150) and pd.PAYDESNUM in (3) then 'CLDT'
       When w3.CODE in (2112,2118,2119,2120,2121,2122,2126,2129,2138,2150,2151) and pd.PAYDESNUM in (2) then 'CLOT'
       When w3.CODE in (2120,2121,2122,2126,2129,2151) and pd.PAYDESNUM in (1,4,5,6,7,9,10,15,17,24,29) then 'CLPT'
       When w3.CODE in (2118,2136,2138,2141) and pd.PAYDESNUM in (20) then 'COMP'
       When w3.CODE in (2176) and pd.PAYDESNUM in (1) then 'CWST'
       When w3.CODE in (2141) and pd.PAYDESNUM in (1,4,5,6,7,9,10,11,15,16,17,18,29) then 'OCFT'
       When w3.CODE in (2141,2142) and pd.PAYDESNUM in (2) then 'OCOT'
       When w3.CODE in (2158) and pd.PAYDESNUM in (1,9,10,17,24) then 'OCPT'
       When w3.CODE in (2136,2160,2158,2159) and pd.PAYDESNUM in (2) then 'OVTM'
       When w3.CODE in (2144,2160) and pd.PAYDESNUM in (3) then 'PPDT'
       When w3.CODE in (2144) and pd.PAYDESNUM in (1,4,5,6,7,9,10,11,15,16,17,18,21,29) then 'PPFT'
       When w3.CODE in (2144,2145) and pd.PAYDESNUM in (2) then 'PPOT'
       When w3.CODE in (2137,2145) and pd.PAYDESNUM in (1,4,5,6,7,9,10,17,24,29) then 'PPPT'
       When w3.CODE in (2142) and pd.PAYDESNUM in (1,4,5,6,7,9,10,15,17,24,29) then 'PTOC'
       When w3.CODE in (2162) and pd.PAYDESNUM in (13) then 'SPRM'
       When w3.CODE in (2118,2119,2136,2138,2141,2144,2150,2155,2156,2185) and pd.PAYDESNUM in (12) then 'STDP'
       When w3.CODE in (2177) and pd.PAYDESNUM in (1,24) then 'STUD'
       When w3.CODE in (2176) and pd.PAYDESNUM in (4,24,29) then 'STUD'
       When w3.CODE in (2110,2112,2128,2119,2120,2121,2122,2126,2129,2136,2137,2141,2142,2144,2145,2150,2151,2155,2156,2157,2158,2159,2160,2162,2175,2176,2177,2185) and pd.PAYDESNUM in (26) then 'EFML'
       When w3.CODE in (2110,2112,2128,2119,2120,2121,2122,2126,2129,2136,2137,2141,2142,2144,2145,2150,2151,2155,2156,2157,2158,2159,2160,2162,2175,2176,2177,2185) and pd.PAYDESNUM in (25) then 'EPSL'
       ELSE 'Error Call ATM'
end as PayDesc
, pd.HOURS as HRS

from EMPLOYEES e
Inner JOIN PAYDESHIST pd
on e.filekey = pd.filekey
INNER JOIN WORKGROUP1 w1
on pd.WG1 = w1.WGNUM
INNER JOIN WORKGROUP2 w2
on pd.WG2 = w2.WGNUM
INNER JOIN WORKGROUP3 w3
on pd.WG3 = w3.WGNUM
INNER JOIN WORKGROUP4 w4
on pd.WG4 = w4.WGNUM

where pd.EVENTDATE between @MIN and @MAX and e.ACTIVESTATUS=0
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,2127,2187,2188,2189)
and pd.PAYDESNUM not in (14,22,23,27,30)
) ex

group by ex.BW, ex.MD, ex.ID, ex.Fund, ex.Budget, ex.ObjectCode, ex.Position, ex.PunchDate, ex.PayDesc
order by 3,8

Content Inventory

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