DM Payroll Export

Documentation for the DM payroll export script that generates an export file for DM payroll system with client identification

Parameters

{mindate} - Start date for the export period
{maxdate} - End date for the export period
@ClientID - Client identifier in DM system (default: '61571')

Data Components

The report processes data in three main sections that are combined at the end:

Regular, OT, and Double Time Hours:
- Client and employee identification
- Regular hours (pay designation 1)
- Overtime hours (pay designation 2)
- Double time hours (pay designation 3)
- Department code (from WG2)
Other Hours:
- Client and employee identification
- Other hour types (pay designations 4, 5, 6)
- Hour code mapping
- Department code
Other Earnings:
- Client and employee identification
- Dollar amount earnings (pay designation 30)
- Earnings code mapping
- Department code

Output Format

The export generates columns in the DM system format:

Client ID and employee number
Tracking and check codes
Department and sub-department codes
Regular, OT, and double time hours
Other hour types and codes
Other earnings and codes
Various payroll codes (frequency, FICA, FAN, tax codes)
Location and time codes

Technical Implementation

The script uses:

Temporary tables to store and combine different data types
Multiple INSERT statements to populate the temporary table
DISTINCT selections to avoid duplicate entries
Subqueries to calculate hour totals by type
Explicit NOCOUNT and ANSI_WARNINGS settings
Active filtering for hourly employees (Paytype=0)

T-SQL

dm_payroll_export.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF


DECLARE @MIN DATE,
		@MAX DATE,
		@ClientID varchar(10)
		
SET @MIN={mindate}
SET @MAX={maxdate}

--Enter Client ID From DM
Set @ClientID = '61571'

DECLARE @DM TABLE(CLIENTNUM VARCHAR(10),EENUM VARCHAR(10),
SSN VARCHAR(9),TRACK VARCHAR(1),ALTDEPT VARCHAR(10),ALTSUBDEPT VARCHAR(10),ALTSUBDEPT2 VARCHAR(10),
CHECKCODE VARCHAR(1),ALTRATE VARCHAR(4),CITYCODE VARCHAR(2),SHIFTCODE VARCHAR(10),REG DECIMAL(38,2),
OT DECIMAL(38,2),DBLTIME DECIMAL(38,2),OTHRS DECIMAL(38,2),OTHCODE VARCHAR(3),OTHEARN DECIMAL(38,2), 
OTHEARNCODE VARCHAR(3),FREQUENCY VARCHAR(1),NOFICA VARCHAR(1),FAN VARCHAR(1),TAXCODE VARCHAR(2),
TAXAMT VARCHAR(12),DEDCODE VARCHAR(2),DEDAMT VARCHAR(12),LOC VARCHAR(10),DEP VARCHAR(10),
IN1 VARCHAR(10),OUT1 VARCHAR(10),IN2 VARCHAR(10),OUT2 VARCHAR(10),HRS VARCHAR(10))

----------------------------REG/OT HOURS
INSERT INTO @DM SELECT DISTINCT @ClientID,E.IDNUM,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,
--Reg
(SELECT SUM(PD.HOURS) FROM PAYDESHIST PD WHERE PD.EVENTDATE BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(1)AND
PD.FILEKEY=E.FILEKEY),
--OT
(SELECT SUM(PD.HOURS) FROM PAYDESHIST PD WHERE PD.EVENTDATE BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(2)AND
PD.FILEKEY=E.FILEKEY),
--DBL
(SELECT SUM(PD.HOURS) FROM PAYDESHIST PD WHERE PD.EVENTDATE BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(3)AND
PD.FILEKEY=E.FILEKEY),

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,WG2.CODE,NULL,NULL,NULL,NULL,NULL

FROM WORKGROUP1 WG1,WORKGROUP2 WG2,EMPLOYEES E WHERE E.Paytype=0 AND E.WG2=WG2.WGNUM 

GROUP BY E.IDNUM,WG2.CODE,E.FILEKEY HAVING(SELECT SUM(PD.HOURS) FROM PAYDESHIST PD WHERE PD.EVENTDATE 
BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(1,2,3)AND PD.FILEKEY=E.FILEKEY)>0 

--------------------------OTHER HOURS

INSERT INTO @DM SELECT DISTINCT @ClientID,E.IDNUM,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SUM(PD.HOURS),PD.PAYDESNUM,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
WG2.CODE,NULL,NULL,NULL,NULL,NULL

FROM WORKGROUP1 WG1,WORKGROUP2 WG2,EMPLOYEES E,PAYDESHIST PD WHERE E.Paytype=0 AND E.WG2=WG2.WGNUM AND PD.FILEKEY=E.FILEKEY AND PD.EVENTDATE BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(4,5,6) 

GROUP BY E.IDNUM,WG2.CODE,E.FILEKEY,PD.PAYDESNUM HAVING(SELECT SUM(PD.HOURS) FROM PAYDESHIST PD WHERE PD.EVENTDATE 
BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(4,5,6)AND PD.FILEKEY=E.FILEKEY)>0 


--------------------------OTHER EARNINGS

INSERT INTO @DM SELECT DISTINCT @ClientID,E.IDNUM,NULL,'0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,SUM(PD.DOLLARS),PD.PAYDESNUM,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
WG2.CODE,NULL,NULL,NULL,NULL,NULL

FROM WORKGROUP1 WG1,WORKGROUP2 WG2,EMPLOYEES E,PAYDESHIST PD WHERE E.Paytype=0 AND E.WG2=WG2.WGNUM
AND PD.FILEKEY=E.FILEKEY AND PD.EVENTDATE BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(30) 

GROUP BY E.IDNUM,WG2.CODE,E.FILEKEY,PD.PAYDESNUM HAVING(SELECT SUM(PD.DOLLARS) FROM PAYDESHIST PD WHERE PD.EVENTDATE 
BETWEEN @MIN AND @MAX AND PD.PAYDESNUM IN(30) AND PD.FILEKEY=E.FILEKEY)>0

SELECT CLIENTNUM,EENUM,SSN,TRACK,ALTDEPT,ALTSUBDEPT,ALTSUBDEPT2,CHECKCODE,ALTRATE,CITYCODE,SHIFTCODE,REG,
OT,DBLTIME,OTHRS,OTHCODE,OTHEARN,OTHEARNCODE,FREQUENCY,NOFICA,FAN,TAXCODE,
TAXAMT,DEDCODE,DEDAMT,LOC,DEP,IN1,OUT1,IN2,OUT2 ,HRS FROM @DM ORDER BY 1,2

Content Inventory

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