Autofill Scripts

Scripts:

scripts/utilities/low_autofill.sql
scripts/utilities/low_autofill_cursor.sql
scripts/utilities/tricreekautofill__for_single_date.sql
scripts/utilities/tricreekautofill__for_single_date_and_filekey.sql

Overview

These utility scripts automatically generate time clock punches (transactions) and supervisor edits based on employee schedules. They help maintain data integrity by ensuring scheduled employees have corresponding punch records, and they clean up orphaned edits when schedules are removed. The scripts are designed for different scenarios: bulk processing, single date processing, and individual employee processing.

Parameters

low_autofill.sql

@STARTDATE - The start date for processing (default: '8/13/2023')
Target Pay Classes: 12, 18
Target Employees: Currently limited to FILEKEY 672 for testing

tricreekautofill scripts

Date parameters for specific date processing
Employee filekey parameters for individual processing

Data Components

The autofill scripts perform several key operations:

Edit Cleanup - Removes orphaned AUTOFILL edits from deleted schedules
Transaction Cleanup - Removes orphaned transactions from removed edits
Punch Edit Generation - Creates IN punch edits from schedules
Same-Day Out Punches - Creates OUT punch edits for same-day shifts
Next-Day Out Punches - Creates OUT punch edits for overnight shifts
Transaction Creation - Generates actual punch transactions from edits
Event Logging - Records processing results in the EVENTS table

Output Format

The main autofill script returns a summary message:

Output	Data Type	Description
Results	varchar(255)	Summary of edits and transactions removed/created

Example: "Autofill: 5 Edits Removed; 3 Trans Removed; 12 Edits Created; 10 Trans Created"

Technical Implementation

The scripts use:

Multi-step processing with cleanup followed by creation
Pay period awareness for current and previous periods
Schedule type filtering (SCHTYPE = 0 for regular schedules)
Archive checking to prevent processing archived data
Duplicate prevention logic to avoid creating duplicate edits
Dynamic SQL for flexible processing
Event logging for audit trail and monitoring

Processing Logic

Cleanup Phase: Remove edits/transactions for deleted schedules
Creation Phase: Generate new edits for existing schedules
Transaction Phase: Create punch records from valid edits
Reporting Phase: Log results and return summary

Schedule Handling

Same-day shifts: ENDTIME > STARTTIME
Overnight shifts: ENDTIME < STARTTIME (spans midnight)
Grace periods: Built-in logic for schedule variations

Notes

Pay Period Aware: Processes current and previous pay periods automatically
Archive Protection: Prevents processing of archived data
Employee Eligibility: Filters by pay class and other criteria
Schedule Types: Only processes regular schedules (SCHTYPE = 0)
Duplicate Prevention: Checks for existing edits before creating new ones
Transaction Safety: Only creates transactions where no conflicts exist
Event Logging: Records all processing activity for audit purposes
Testing Mode: Main script currently limited to specific employees for safety
Overnight Handling: Properly manages shifts that cross midnight
Performance Optimized: Uses efficient queries and indexing strategies
Remove employee restrictions (FILEKEY IN clause) before production use
Test thoroughly with small employee groups before full deployment
Monitor EVENTS table for processing results and errors
Run during off-peak hours to minimize system impact
Verify pay period dates are correctly configured before processing

T-SQL

low_autofill.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @STARTDATE DATE
DECLARE @EDITSREMOVED smallint  
DECLARE @TRANSREMOVED smallint  
DECLARE @EDITSADDED smallint  
DECLARE @TRANSADDED smallint  
DECLARE @RESULTS varchar(255)  
  
Set @STARTDATE = '8/13/2023' -- Delcare the start date of this script
SELECT @EDITSREMOVED = 0  
SELECT @TRANSREMOVED = 0  
SELECT @EDITSADDED = 0  
SELECT @TRANSADDED = 0  
SELECT @RESULTS = ''   
  
--CLEAR OUT EDITS FROM SCHEDULES REMOVED
DELETE S  
FROM SUPEDITS S  
WHERE USERNAME = 'AUTOFILL'  
AND CLKSUP > 0   
AND ((SELECT COUNT(*) FROM SCHEDULES WHERE UNIQUEID = S.CLKSUP) = 0)  
AND S.EFFDATE >= @STARTDATE  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
  
SELECT @EDITSREMOVED = @@ROWCOUNT  --Record how many edits were removed
  
--CLEAR PUNCHES FROM EDITS REMOVED
DELETE T  
FROM TRANSACTIONS T  
WHERE STATION = 99  
AND ((SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 0 AND USERNAME = 'AUTOFILL' AND FILEKEY = T.FILEKEY AND T.DTTMSTAMP = DATEADD(hh, datepart(hh, efftime), dateadd(n, datepart(n, efftime), effdate)))=0)  
AND T.DTTMSTAMP >= @STARTDATE  
AND TRANSTYPE = 0  
AND (SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 13 AND T.DTTMSTAMP = DATEADD(hh, datepart(hh, efftime), dateadd(n, datepart(n, efftime), effdate)) AND FILEKEY = T.FILEKEY) = 0  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY  AND T.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND T.DTTMSTAMP BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)

SELECT @TRANSREMOVED = @@ROWCOUNT  --Record how many transaction were removed
  
--INSERT IN PUNCH EDITS FROM SCHEDULES
INSERT INTO SUPEDITS  (FILEKEY,EDITTYPE,EFFDATE,EFFTIME,USERNAME,CLKSUP,WG1,WG2,WG3,PREVWG1,PREVWG2,PREVWG3,PAYDESNUM,PREVPAYDES,HOURS,RATE,DOLLARS,TSDATETIME,REASON,SITE,CANCEL,CANCELBY,CANCELON,PREVEFFDATE,PREVEFFTIME,WG4,PREVWG4,WG5,PREVWG5,WG6,PREVWG6,WG7,PREVWG7)
SELECT A.FILEKEY, 0 AS EDITTYPE, A.schdate, A.starttime, 'Autofill' AS USERNAME, A.UNIQUEID AS CLKSUP, 0 AS WG1, 0 AS WG2, 0 AS WG3, 0 AS PREVWG1, 0 AS PREVWG2, 0 AS PREVWG3, 0 AS PAYDESNUM, 0 AS PREVPAYDES, 0 AS HOURS, 0.0 AS RATE, 0.0 AS DOLLARS, GETDATE() AS TSDATETIME, 0 AS REASON, 0 AS SITE, 0 AS CANCEL, '' AS CANCELBY, GETDATE() AS CANCELON, GETDATE() AS PREVEFFDATE, A.starttime AS PREVEFFTIME, 0 AS WG4, 0 AS PREVWG4, 0 AS WG5, 0 AS PREVWG5, 0 AS WG6, 0 AS PREVWG6, 0 AS WG7, 0 AS PREVWG7
from   
(select s.uniqueid, e.filekey, s.schdate, s.starttime, s.endtime from schedules s, employees e, payperiodinfo p  
where s.filekey = e.filekey  
and schdate between p.begindate and p.enddate  
and (  
GETDATE() between p.begindate and p.enddate  
OR   
DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate  
)  
AND S.SCHDATE >= @STARTDATE  
and e.PAYCLASS in (12,18)   -----ADD in employee eligiblity
and e.FILEKEY in (672) --Temp run on 1 emp
and s.schtype = 0  
AND S.SCHDATE <= GETDATE() AND ((SELECT COUNT(*) FROM SUPEDITS WHERE USERNAME = 'AUTOFILL' AND EFFTIME = S.STARTTIME AND EFFDATE = S.SCHDATE AND FILEKEY = S.FILEKEY) = 0)  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.SCHDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
) A LEFT OUTER JOIN SCHEDULES S  
ON A.FILEKEY = S.FILEKEY  
AND A.SCHDATE = S.SCHDATE  
AND A.STARTTIME = S.ENDTIME  
WHERE S.UNIQUEID IS NULL  
ORDER BY A.SCHDATE  

SELECT @EDITSADDED = @@ROWCOUNT  --Record how many In Punches were added via supedits
  
--INSERT SAME DAY OUT PUNCH EDITS FROM SCHEDULES  
INSERT INTO SUPEDITS  (FILEKEY,EDITTYPE,EFFDATE,EFFTIME,USERNAME,CLKSUP,WG1,WG2,WG3,PREVWG1,PREVWG2,PREVWG3,PAYDESNUM,PREVPAYDES,HOURS,RATE,DOLLARS,TSDATETIME,REASON,SITE,CANCEL,CANCELBY,CANCELON,PREVEFFDATE,PREVEFFTIME,WG4,PREVWG4,WG5,PREVWG5,WG6,PREVWG6,WG7,PREVWG7)
SELECT A.FILEKEY, 0 AS EDITTYPE, A.schdate, A.starttime, 'Autofill' AS USERNAME, A.UNIQUEID AS CLKSUP, 0 AS WG1, 0 AS WG2, 0 AS WG3, 0 AS PREVWG1, 0 AS PREVWG2, 0 AS PREVWG3, 0 AS PAYDESNUM, 0 AS PREVPAYDES, 0 AS HOURS, 0.0 AS RATE, 0.0 AS DOLLARS, GETDATE() AS TSDATETIME, 0 AS REASON, 0 AS SITE, 0 AS CANCEL, '' AS CANCELBY, GETDATE() AS CANCELON, GETDATE() AS PREVEFFDATE, A.starttime AS PREVEFFTIME, 0 AS WG4, 0 AS PREVWG4, 0 AS WG5, 0 AS PREVWG5, 0 AS WG6, 0 AS PREVWG6, 0 AS WG7, 0 AS PREVWG7  
from   
(select s.uniqueid, e.filekey, s.schdate, s.starttime, s.endtime   
from schedules s, employees e, payperiodinfo p  
where s.filekey = e.filekey  
and schdate between p.begindate and p.enddate  
and (  
GETDATE() between p.begindate and p.enddate  
OR   
DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate  
)  
AND S.SCHDATE >= @STARTDATE  
and e.PAYCLASS in (12,18)   -----ADD in employee eligiblity
and e.FILEKEY in (672) --Temp run on 1 emp
and s.endtime > s.starttime  
and s.schtype = 0  
AND S.SCHDATE <= GETDATE()   
AND ((SELECT COUNT(*) FROM SUPEDITS WHERE USERNAME = 'AUTOFILL' AND EFFTIME = S.ENDTIME AND EFFDATE = S.SCHDATE AND FILEKEY = S.FILEKEY) = 0)  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.SCHDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
) A LEFT OUTER JOIN SCHEDULES S  
ON A.FILEKEY = S.FILEKEY  
AND A.SCHDATE = S.SCHDATE  
AND A.ENDTIME = S.STARTTIME  
WHERE S.UNIQUEID IS NULL  
ORDER BY A.SCHDATE 

SELECT @EDITSADDED = @@ROWCOUNT + @EDITSADDED  --Add Punch Out Edits to total of Edits added via supedits
  
--INSERT NEXT DAY OUT PUNCH EDITS FROM SCHEDULES
INSERT INTO SUPEDITS  (FILEKEY,EDITTYPE,EFFDATE,EFFTIME,USERNAME,CLKSUP,WG1,WG2,WG3,PREVWG1,PREVWG2,PREVWG3,PAYDESNUM,PREVPAYDES,HOURS,RATE,DOLLARS,TSDATETIME,REASON,SITE,CANCEL,CANCELBY,CANCELON,PREVEFFDATE,PREVEFFTIME,WG4,PREVWG4,WG5,PREVWG5,WG6,PREVWG6,WG7,PREVWG7)
SELECT A.FILEKEY, 0 AS EDITTYPE, A.schdate, A.starttime, 'Autofill' AS USERNAME, A.UNIQUEID AS CLKSUP, 0 AS WG1, 0 AS WG2, 0 AS WG3, 0 AS PREVWG1, 0 AS PREVWG2, 0 AS PREVWG3, 0 AS PAYDESNUM, 0 AS PREVPAYDES, 0 AS HOURS, 0.0 AS RATE, 0.0 AS DOLLARS, GETDATE() AS TSDATETIME, 0 AS REASON, 0 AS SITE, 0 AS CANCEL, '' AS CANCELBY, GETDATE() AS CANCELON, GETDATE() AS PREVEFFDATE, A.starttime AS PREVEFFTIME, 0 AS WG4, 0 AS PREVWG4, 0 AS WG5, 0 AS PREVWG5, 0 AS WG6, 0 AS PREVWG6, 0 AS WG7, 0 AS PREVWG7
from   
(select s.uniqueid, e.filekey, s.schdate, s.starttime, s.endtime   
from schedules s, employees e, payperiodinfo p  
where s.filekey = e.filekey  
and schdate between p.begindate and p.enddate  
and   
(  
GETDATE() between p.begindate and p.enddate  
OR   
DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate  
)  
AND S.SCHDATE >= @STARTDATE  
and e.PAYCLASS in (12,18)   -----ADD in employee eligiblity
and e.FILEKEY in (672) --Temp run on 1 emp
and s.endtime < s.starttime  
and s.schtype = 0  
AND S.SCHDATE <= GETDATE()   
AND ((SELECT COUNT(*) FROM SUPEDITS WHERE USERNAME = 'AUTOFILL' AND EFFTIME = S.ENDTIME AND EFFDATE = dateadd(d, 1, S.schdate) AND FILEKEY = S.FILEKEY) = 0)  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.SCHDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
) A LEFT OUTER JOIN SCHEDULES S  
ON A.FILEKEY = S.FILEKEY  
AND A.SCHDATE = S.SCHDATE  
AND A.ENDTIME = S.STARTTIME  
WHERE S.UNIQUEID IS NULL  
ORDER BY A.SCHDATE  

SELECT @EDITSADDED = @@ROWCOUNT + @EDITSADDED  --Add Punch Out Edits to total of Edits added via supedits
  
--CREATE TRANSACTIONS BASED ON EDITS, WHERE NO OTHER EDIT EXISTS AGAINST THAT DATE/TIME (EDITS 1[DELETE] AND 13[CHANGE])
INSERT INTO TRANSACTIONS (FILEKEY,DTTMSTAMP,TRANSTYPE,STATION,PERIOD) 
SELECT FILEKEY, DATEADD(hh, datepart(hh, efftime), dateadd(n, datepart(n, efftime), effdate)), 0, 99, 0   
FROM SUPEDITS S, PAYPERIODINFO P  
WHERE S.EFFDATE BETWEEN P.BEGINDATE AND P.ENDDATE  
AND S.EFFDATE >= @STARTDATE  
AND  
(  
GETDATE() between p.begindate and p.enddate  
OR   
DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate  
)  
AND S.USERNAME = 'AUTOFILL'  
AND (SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 1 AND EFFDATE = S.EFFDATE AND EFFTIME = S.EFFTIME AND FILEKEY = S.FILEKEY) = 0  
AND (SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 13 AND EFFDATE = S.EFFDATE AND S.EFFTIME = PREVEFFTIME AND FILEKEY = S.FILEKEY) = 0  
AND (SELECT COUNT(*) FROM TRANSACTIONS WHERE DTTMSTAMP = DATEADD(hh, datepart(hh, S.efftime), dateadd(n, datepart(n, S.efftime), S.effdate)) AND FILEKEY = S.FILEKEY)= 0  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.EDITTYPE = 10)=0)  
ORDER BY 1, 2  

SELECT @TRANSADDED = @@ROWCOUNT  --record how many Tracation were added
  
SELECT @RESULTS = 'Autofill: ' + CAST(@EDITSREMOVED AS VARCHAR(20))+' Edits Removed; ' + CAST(@TRANSREMOVED AS VARCHAR(20)) + ' Trans Removed; ' + CAST(@EDITSADDED AS VARCHAR(20)) + ' Edits Created; ' + CAST(@TRANSADDED AS VARCHAR(20)) + ' Trans Created' 
 
INSERT INTO EVENTS SELECT GETDATE(), 8, 0, 0, 0, 0, '', '', '', @RESULTS, 0x0  

SET NOCOUNT OFF  
  
SELECT @RESULTS

low_autofill_cursor.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
 
DECLARE @filekey int,
		@STARTDATE DATE,
		@TRANSADDED smallint,  
		@RESULTS varchar(255)  

Declare @EDITSREMOVED table (Filekey int, Count Int)
Declare @TRANSREMOVED table (Filekey int, Count Int)
Declare @INPUNCHADDED table (Filekey int, Count Int)
Declare @OUTPUNCHADDED table (Filekey int, Count Int)
  
Set @STARTDATE = '8/13/2023' -- Delcare the start date of this script
SET @TRANSADDED = 0  
SELECT @RESULTS = ''   

DECLARE employees_cursor CURSOR FOR

Select Filekey from EMPLOYEES where Payclass in (12,18) order by FILEKEY

Open employees_cursor

FETCH NEXT FROM employees_cursor INTO
	@filekey

While @@FETCH_STATUS = 0
	Begin		
		--CLEAR OUT EDITS FROM SCHEDULES REMOVED
		DELETE S  
		FROM SUPEDITS S  
		WHERE s.FILEKEY = @filekey		
		AND USERNAME = 'AUTOFILL'  
		AND CLKSUP > 0   
		AND ((SELECT COUNT(*) FROM SCHEDULES WHERE FILEKEY = @filekey and UNIQUEID = S.CLKSUP) = 0)  
		AND S.EFFDATE >= @STARTDATE  
		AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
  
		Insert into @EDITSREMOVED Select @filekey, @@ROWCOUNT  --Record how many edits were removed
		
		--CLEAR PUNCHES FROM EDITS REMOVED
		DELETE T  
		FROM TRANSACTIONS T  
		WHERE t.FILEKEY = @filekey
		AND STATION = 99  
		AND ((SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 0 AND USERNAME = 'AUTOFILL' AND FILEKEY = T.FILEKEY AND T.DTTMSTAMP = DATEADD(hh, datepart(hh, efftime), dateadd(n, datepart(n, efftime), effdate)))=0)  
		AND T.DTTMSTAMP >= @STARTDATE  
		AND TRANSTYPE = 0  
		AND (SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 13 AND T.DTTMSTAMP = DATEADD(hh, datepart(hh, efftime), dateadd(n, datepart(n, efftime), effdate)) AND FILEKEY = T.FILEKEY) = 0  
		AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY  AND T.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND T.DTTMSTAMP BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)

		Insert into @TRANSREMOVED Select @filekey, @@ROWCOUNT  --Record how many transaction were removed

		--INSERT IN PUNCH EDITS FROM SCHEDULES
		INSERT INTO SUPEDITS  (FILEKEY,EDITTYPE,EFFDATE,EFFTIME,USERNAME,CLKSUP,WG1,WG2,WG3,PREVWG1,PREVWG2,PREVWG3,PAYDESNUM,PREVPAYDES,HOURS,RATE,DOLLARS,TSDATETIME,REASON,SITE,CANCEL,CANCELBY,CANCELON,PREVEFFDATE,PREVEFFTIME,WG4,PREVWG4,WG5,PREVWG5,WG6,PREVWG6,WG7,PREVWG7)
		SELECT A.FILEKEY, 0 AS EDITTYPE, A.schdate, A.starttime, 'Autofill' AS USERNAME, A.UNIQUEID AS CLKSUP, 0 AS WG1, 0 AS WG2, 0 AS WG3, 0 AS PREVWG1, 0 AS PREVWG2, 0 AS PREVWG3, 0 AS PAYDESNUM, 0 AS PREVPAYDES, 0 AS HOURS, 0.0 AS RATE, 0.0 AS DOLLARS, GETDATE() AS TSDATETIME, 0 AS REASON, 0 AS SITE, 0 AS CANCEL, '' AS CANCELBY, GETDATE() AS CANCELON, GETDATE() AS PREVEFFDATE, A.starttime AS PREVEFFTIME, 0 AS WG4, 0 AS PREVWG4, 0 AS WG5, 0 AS PREVWG5, 0 AS WG6, 0 AS PREVWG6, 0 AS WG7, 0 AS PREVWG7
		from   
		(select s.uniqueid, e.filekey, s.schdate, s.starttime, s.endtime from schedules s, employees e, payperiodinfo p  
		where s.filekey = e.filekey  
		and schdate between p.begindate and p.enddate  
		and ( GETDATE() between p.begindate and p.enddate  OR DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate )  
		AND S.SCHDATE >= @STARTDATE  
		and e.FILEKEY = @filekey
		and s.schtype = 0  
		AND S.SCHDATE <= GETDATE() AND ((SELECT COUNT(*) FROM SUPEDITS WHERE USERNAME = 'AUTOFILL' AND EFFTIME = S.STARTTIME AND EFFDATE = S.SCHDATE AND FILEKEY = S.FILEKEY) = 0)  
		AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.SCHDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
		) A LEFT OUTER JOIN SCHEDULES S  
		ON A.FILEKEY = S.FILEKEY  
		AND A.SCHDATE = S.SCHDATE  
		AND A.STARTTIME = S.ENDTIME  
		WHERE S.UNIQUEID IS NULL  
		ORDER BY A.SCHDATE  

		Insert into @INPUNCHADDED Select @filekey, @@ROWCOUNT  --Record how many In punches were addded.

		--INSERT SAME DAY OUT PUNCH EDITS FROM SCHEDULES  
		INSERT INTO SUPEDITS  (FILEKEY,EDITTYPE,EFFDATE,EFFTIME,USERNAME,CLKSUP,WG1,WG2,WG3,PREVWG1,PREVWG2,PREVWG3,PAYDESNUM,PREVPAYDES,HOURS,RATE,DOLLARS,TSDATETIME,REASON,SITE,CANCEL,CANCELBY,CANCELON,PREVEFFDATE,PREVEFFTIME,WG4,PREVWG4,WG5,PREVWG5,WG6,PREVWG6,WG7,PREVWG7)
		SELECT A.FILEKEY, 0 AS EDITTYPE, A.schdate, A.ENDTIME, 'Autofill' AS USERNAME, A.UNIQUEID AS CLKSUP, 0 AS WG1, 0 AS WG2, 0 AS WG3, 0 AS PREVWG1, 0 AS PREVWG2, 0 AS PREVWG3, 0 AS PAYDESNUM, 0 AS PREVPAYDES, 0 AS HOURS, 0.0 AS RATE, 0.0 AS DOLLARS, GETDATE() AS TSDATETIME, 0 AS REASON, 0 AS SITE, 0 AS CANCEL, '' AS CANCELBY, GETDATE() AS CANCELON, GETDATE() AS PREVEFFDATE, A.starttime AS PREVEFFTIME, 0 AS WG4, 0 AS PREVWG4, 0 AS WG5, 0 AS PREVWG5, 0 AS WG6, 0 AS PREVWG6, 0 AS WG7, 0 AS PREVWG7  
		from   
		(select s.uniqueid, e.filekey, s.schdate, s.starttime, s.endtime   
		from schedules s, employees e, payperiodinfo p  
		where s.filekey = e.filekey  
		and schdate between p.begindate and p.enddate  
		and ( GETDATE() between p.begindate and p.enddate OR DATEADD(d, -14, GETDATE()) between p.begindate and p.enddate )  
		AND S.SCHDATE >= @STARTDATE  
		and e.FILEKEY = @filekey
		and s.endtime > s.starttime  
		and s.schtype = 0  
		AND S.SCHDATE <= GETDATE()   
		AND ((SELECT COUNT(*) FROM SUPEDITS WHERE USERNAME = 'AUTOFILL' AND EFFTIME = S.ENDTIME AND EFFDATE = S.SCHDATE AND FILEKEY = S.FILEKEY) = 0)  
		AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A WHERE X.FILEKEY = A.FILEKEY AND S.FILEKEY = A.FILEKEY AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND S.SCHDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND AND X.EDITTYPE = 10)=0)  
		) A LEFT OUTER JOIN SCHEDULES S  
		ON A.FILEKEY = S.FILEKEY  
		AND A.SCHDATE = S.SCHDATE  
		AND A.ENDTIME = S.STARTTIME  
		WHERE S.UNIQUEID IS NULL  
		ORDER BY A.SCHDATE 

		Insert into @OUTPUNCHADDED Select @filekey, @@ROWCOUNT  --Record how many Out punches were addded.

		--Grab Next employee
		FETCH NEXT FROM employees_cursor INTO @filekey
	End

Close Employees_cursor

Deallocate Employees_cursor

Select e.lastname+', '+e.firstname, er.Count as EditsRemoved, tr.Count as TransRemoved, IPA.Count as InPunchAdded from employees e
left join @EDITSREMOVED er
on e.filekey = er.Filekey
left join @TRANSREMOVED tr
on e.FILEKEY = tr.Filekey
left join @INPUNCHADDED IPA
on e.FILEKEY = IPA.Filekey

tricreekautofill__for_single_date.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @filekey INT, @TARGETDATE DATE;

-- Set the target date to process (Change this value as needed)
SET @TARGETDATE = '2025-03-12'; -- Input your specific date

DECLARE @EDITSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @TRANSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @INPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @OUTPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @TRANSADDED TABLE (Filekey INT, Count INT);

DECLARE employees_cursor CURSOR FOR
SELECT Filekey FROM EMPLOYEES 
WHERE Payclass IN (12, 18) 
ORDER BY FILEKEY;

OPEN employees_cursor;

FETCH NEXT FROM employees_cursor INTO @filekey;

WHILE @@FETCH_STATUS = 0
BEGIN		
	-- 🔹 Clear out edits from schedules removed for @TARGETDATE only
	DELETE FROM SUPEDITS 
	WHERE FILEKEY = @filekey		
	AND USERNAME = 'AUTOFILL'  
	AND CLKSUP > 0   
	AND EFFDATE = @TARGETDATE  
	AND ((SELECT COUNT(*) FROM SCHEDULES WHERE FILEKEY = @filekey AND UNIQUEID = CLKSUP AND SCHDATE = @TARGETDATE) = 0)  
	AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
		  WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @filekey 
		  AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
		  AND X.EDITTYPE = 10) = 0);  

	INSERT INTO @EDITSREMOVED SELECT @filekey, @@ROWCOUNT;  

	-- 🔹 Clear punches from edits removed for @TARGETDATE only
	DELETE FROM TRANSACTIONS  
	WHERE FILEKEY = @filekey
	AND STATION = 99  
	AND DTTMSTAMP BETWEEN @TARGETDATE AND DATEADD(DAY, 1, @TARGETDATE)  
	AND TRANSTYPE = 0  
	AND ((SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 0 AND USERNAME = 'AUTOFILL' 
		  AND FILEKEY = FILEKEY AND EFFDATE = @TARGETDATE) = 0)  
	AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
		  WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @filekey 
		  AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
		  AND X.EDITTYPE = 10) = 0);

	INSERT INTO @TRANSREMOVED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Insert IN punches for @TARGETDATE
	INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
	SELECT A.FILEKEY, 0, A.SCHDATE, A.STARTTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
	FROM SCHEDULES A
	WHERE A.FILEKEY = @filekey
	AND A.SCHDATE = @TARGETDATE  
	AND A.SCHTYPE = 0  
	AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.STARTTIME AND USERNAME = 'AUTOFILL');

	INSERT INTO @INPUNCHADDED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Insert OUT punches for @TARGETDATE
	INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
	SELECT A.FILEKEY, 0, A.SCHDATE, A.ENDTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
	FROM SCHEDULES A
	WHERE A.FILEKEY = @filekey
	AND A.SCHDATE = @TARGETDATE  
	AND A.ENDTIME > A.STARTTIME  
	AND A.SCHTYPE = 0  
	AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.ENDTIME AND USERNAME = 'AUTOFILL');

	INSERT INTO @OUTPUNCHADDED SELECT @filekey, @@ROWCOUNT;

	-- 🔹 Create transactions for @TARGETDATE
	INSERT INTO TRANSACTIONS (FILEKEY, DTTMSTAMP, TRANSTYPE, STATION, PERIOD) 
	SELECT FILEKEY, DATEADD(HOUR, DATEPART(HOUR, EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, EFFTIME), EFFDATE)), 0, 99, 0  
	FROM SUPEDITS S
	WHERE S.FILEKEY = @filekey
	AND S.EFFDATE = @TARGETDATE  
	AND S.USERNAME = 'AUTOFILL'  
	AND NOT EXISTS (SELECT 1 FROM TRANSACTIONS WHERE FILEKEY = S.FILEKEY AND DTTMSTAMP = DATEADD(HOUR, DATEPART(HOUR, S.EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, S.EFFTIME), S.EFFDATE)) );

	INSERT INTO @TRANSADDED SELECT @filekey, @@ROWCOUNT;

	-- Fetch next employee
	FETCH NEXT FROM employees_cursor INTO @filekey;
END

CLOSE employees_cursor;
DEALLOCATE employees_cursor;

-- 🔹 Report results
SELECT e.lastname + ', ' + e.firstname AS Employee, 
       er.Count AS EditsRemoved, 
       tr.Count AS TransRemoved, 
       IPA.Count AS InPunchAdded, 
       OPA.Count AS OutPunchAdded, 
       Ta.Count AS TransAdded 
FROM EMPLOYEES e
LEFT JOIN @EDITSREMOVED er ON e.filekey = er.Filekey
LEFT JOIN @TRANSREMOVED tr ON e.filekey = tr.Filekey
LEFT JOIN @INPUNCHADDED IPA ON e.filekey = IPA.Filekey
LEFT JOIN @OUTPUNCHADDED OPA ON e.filekey = OPA.Filekey
LEFT JOIN @TRANSADDED TA ON e.filekey = TA.filekey
WHERE e.Payclass IN (12,18);

tricreekautofill__for_single_date_and_filekey.sql

SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;

DECLARE @TARGETDATE DATE, @TARGETFILEKEY INT;

-- Set the target date and filekey (Change these values as needed)
SET @TARGETDATE = '2025-03-12';  -- Input the specific date
SET @TARGETFILEKEY = 701;      -- Input the specific FileKey (Employee ID)

DECLARE @EDITSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @TRANSREMOVED TABLE (Filekey INT, Count INT);
DECLARE @INPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @OUTPUNCHADDED TABLE (Filekey INT, Count INT);
DECLARE @TRANSADDED TABLE (Filekey INT, Count INT);

-- 🔹 Clear out edits from schedules removed for @TARGETDATE and @TARGETFILEKEY
DELETE FROM SUPEDITS 
WHERE FILEKEY = @TARGETFILEKEY
AND USERNAME = 'AUTOFILL'  
AND CLKSUP > 0   
AND EFFDATE = @TARGETDATE  
AND ((SELECT COUNT(*) FROM SCHEDULES WHERE FILEKEY = @TARGETFILEKEY AND UNIQUEID = CLKSUP AND SCHDATE = @TARGETDATE) = 0)  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
      WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @TARGETFILEKEY 
      AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
      AND X.EDITTYPE = 10) = 0);

INSERT INTO @EDITSREMOVED SELECT @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Clear punches from edits removed
DELETE FROM TRANSACTIONS  
WHERE FILEKEY = @TARGETFILEKEY
AND STATION = 99  
AND DTTMSTAMP BETWEEN @TARGETDATE AND DATEADD(DAY, 1, @TARGETDATE)  
AND TRANSTYPE = 0  
AND ((SELECT COUNT(*) FROM SUPEDITS WHERE EDITTYPE = 0 AND USERNAME = 'AUTOFILL' 
      AND FILEKEY = FILEKEY AND EFFDATE = @TARGETDATE) = 0)  
AND ((SELECT COUNT(*) FROM SUPEDITS X, ARCHIVES A 
      WHERE X.FILEKEY = A.FILEKEY AND A.FILEKEY = @TARGETFILEKEY 
      AND X.EFFDATE BETWEEN A.PERIODBEGIN AND A.PERIODEND 
      AND X.EDITTYPE = 10) = 0);

INSERT INTO @TRANSREMOVED SELECT @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Insert IN punches
INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
SELECT A.FILEKEY, 0, A.SCHDATE, A.STARTTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
FROM SCHEDULES A
WHERE A.FILEKEY = @TARGETFILEKEY
AND A.SCHDATE = @TARGETDATE  
AND A.SCHTYPE = 0  
AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.STARTTIME AND USERNAME = 'AUTOFILL');

INSERT INTO @INPUNCHADDED SELECT @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Insert OUT punches
INSERT INTO SUPEDITS (FILEKEY, EDITTYPE, EFFDATE, EFFTIME, USERNAME, CLKSUP, WG1, WG2, WG3, PREVWG1, PREVWG2, PREVWG3, PAYDESNUM, PREVPAYDES, HOURS, RATE, DOLLARS, TSDATETIME, REASON, SITE, CANCEL, CANCELBY, CANCELON, PREVEFFDATE, PREVEFFTIME, WG4, PREVWG4, WG5, PREVWG5, WG6, PREVWG6, WG7, PREVWG7)
SELECT A.FILEKEY, 0, A.SCHDATE, A.ENDTIME, 'Autofill', A.UNIQUEID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, GETDATE(), 0, 0, 0, '', GETDATE(), GETDATE(), A.STARTTIME, 0, 0, 0, 0, 0, 0, 0, 0
FROM SCHEDULES A
WHERE A.FILEKEY = @TARGETFILEKEY
AND A.SCHDATE = @TARGETDATE  
AND A.ENDTIME > A.STARTTIME  
AND A.SCHTYPE = 0  
AND NOT EXISTS (SELECT 1 FROM SUPEDITS WHERE FILEKEY = A.FILEKEY AND EFFDATE = A.SCHDATE AND EFFTIME = A.ENDTIME AND USERNAME = 'AUTOFILL');

INSERT INTO @OUTPUNCHADDED SELECT @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Create transactions for @TARGETDATE
INSERT INTO TRANSACTIONS (FILEKEY, DTTMSTAMP, TRANSTYPE, STATION, PERIOD) 
SELECT FILEKEY, DATEADD(HOUR, DATEPART(HOUR, EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, EFFTIME), EFFDATE)), 0, 99, 0  
FROM SUPEDITS S
WHERE S.FILEKEY = @TARGETFILEKEY
AND S.EFFDATE = @TARGETDATE  
AND S.USERNAME = 'AUTOFILL'  
AND NOT EXISTS (SELECT 1 FROM TRANSACTIONS WHERE FILEKEY = S.FILEKEY AND DTTMSTAMP = DATEADD(HOUR, DATEPART(HOUR, S.EFFTIME), DATEADD(MINUTE, DATEPART(MINUTE, S.EFFTIME), S.EFFDATE)) );

INSERT INTO @TRANSADDED SELECT @TARGETFILEKEY, @@ROWCOUNT;

-- 🔹 Report results for the specified employee
SELECT e.lastname + ', ' + e.firstname AS Employee, 
       er.Count AS EditsRemoved, 
       tr.Count AS TransRemoved, 
       IPA.Count AS InPunchAdded, 
       OPA.Count AS OutPunchAdded, 
       Ta.Count AS TransAdded 
FROM EMPLOYEES e
LEFT JOIN @EDITSREMOVED er ON e.filekey = er.Filekey
LEFT JOIN @TRANSREMOVED tr ON e.filekey = tr.Filekey
LEFT JOIN @INPUNCHADDED IPA ON e.filekey = IPA.Filekey
LEFT JOIN @OUTPUNCHADDED OPA ON e.filekey = OPA.Filekey
LEFT JOIN @TRANSADDED TA ON e.filekey = TA.filekey
WHERE e.Payclass IN (12,18)
AND e.Filekey = @TARGETFILEKEY;

Content Inventory

Doc File: content/docs/utilities/autofill_scripts.mdx
SQL Script: SQL/utilities/low_autofill.sql

Autofill Scripts

On this page