Write Rolling 6-Week Average to Custom Field

Overview

This utility script calculates a rolling 6-week average of work hours for all active employees and stores the result in a custom field (FIELDID=2) in the EMPLOYEEFIELDS table. It analyzes pay designation history over the most recent 6-week period, computes the average weekly hours, and updates employee custom fields with this calculated value. The script is useful for tracking employee work patterns and capacity planning.

Parameters

This script has no input parameters - it processes all active employees using a dynamically calculated 6-week period ending with the current week.

Data Components

The script performs several key operations:

1. Week Calculation - Determines the 6-week period ending with current week
2. Employee Processing - Iterates through all active employees
3. Hours Aggregation - Sums work hours from specific pay designations
4. Average Calculation - Computes 6-week average (total hours ÷ 6)
5. Custom Field Update - Stores average in EMPLOYEEFIELDS table
6. Results Reporting - Shows processing results for verification

Output Format

The script returns processing results for employees with calculated averages:

Only employees with averages > 0 are included in the results.

Technical Implementation

The script uses:

• Common Table Expression (CTE) for dynamic week calculation
• Cursor-based processing for individual employee calculations
• Table variables for week tracking and results storage
• Conditional updates to existing custom field records
• Pay designation filtering for specific hour types

Week Calculation Logic

1. Current Week Start: Uses DATEPART(dw, GETDATE()) to find week beginning
2. 6-Week Range: Calculates 6 weeks back from current week
3. Date Range: From week 1 start to week 6 end (42-day period)

Pay Designation Types Included

• 1, 2: Regular work hours
• 8: Overtime hours
• 23, 24, 25: Additional work categories

Custom Field Management

• Field ID: 2 (designated for rolling average)
• Update Mode: Updates existing records only
• Data Type: Stored as ASFLOAT in EMPLOYEEFIELDS
• Commented Insert: New record creation is disabled

Notes

• Active Employees Only: Processes only employees with ACTIVESTATUS = 0
• 6-Week Window: Uses rolling 6-week period ending with current week
• Selective Pay Types: Only includes specific pay designation numbers
• Custom Field Storage: Updates FIELDID = 2 in EMPLOYEEFIELDS table
• Update Only Mode: Only updates existing custom field records
• Precision: Calculations rounded to 2 decimal places
• Zero Filtering: Only processes employees with hours > 0
• Commented Insert Logic: New record creation is disabled for safety
• Run weekly to maintain current rolling averages
• Verify pay designation numbers match your current system configuration
• Consider enabling the commented INSERT logic if new employee records are needed
• Monitor custom field usage to avoid conflicts with other processes
• Test calculations manually for a few employees to verify accuracy
• Consider the impact on reports that use this custom field data

T-SQL

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare 
	@W1start datetime, @W6end datetime,
	@filekey int,
	@WHours Decimal(38,2),
	@AVG Decimal(38,2)

Declare @Results table (Filekey int, WHours Decimal(38,2), AVG Decimal(38,2))

Declare @weekStart_table table(SDate Date, num int);

WITH week_table AS (SELECT dateadd(day, 1 - datepart(dw,getdate()), cast(getdate() as date)) [Date]
                    UNION ALL 
                    SELECT dateadd(day, -7,[Date])  from week_table WHERE [Date] >= dateadd(WEEK,-6,cast(getdate() as date))   
					)

Insert Into @weekStart_table Select *, ROW_NUMBER() Over (order by date asc) as num from week_table order by Date asc

		Set @W1start = (Select SDate from @weekStart_table where num=1)
		Set @W6end = DATEADD(day,-1,(Select SDate from @weekStart_table where num=7))
	
DECLARE employees_cursor CURSOR FOR

Select Filekey from EMPLOYEES where ACTIVESTATUS=0 order by FILEKEY

Open employees_cursor

FETCH NEXT FROM employees_cursor INTO
	@filekey

While @@FETCH_STATUS = 0
	Begin		

		Select @WHours = Round(Isnull(SUM(Hours),0),2) from PAYDESHIST where FILEKEY=@filekey and EVENTDATE between @W1start and @W6end and PAYDESNUM in (1,2,8,23,24,25)
		Select @AVG = Round(@WHours/6,2)

		If @AVG>0
			Begin
				If Exists (Select UNIQUEID from EMPLOYEEFIELDS where FILEKEY=@filekey and FIELDID=2 Group by UNIQUEID having Count(*)>0) 
					Begin
						Insert into @Results Select @filekey, @WHours, @AVG
						update EMPLOYEEFIELDS set ASFLOAT=@AVG where FILEKEY=@filekey and FIELDID=2
					End
				/*If Exists (Select UNIQUEID from EMPLOYEEFIELDS where FILEKEY=@filekey and FIELDID=2 Group by UNIQUEID having Count(*)=0)
					Begin
						Insert into @Results Select @filekey, @WHours, @AVG
						Insert into EMPLOYEEFIELDS (filekey,FIELDID,ASSTRING,ASINTEGER,ASDATE,ASFLOAT,LASTMODIFIED,MODIFIEDBY)
						VALUES (@filekey,2,'',0,GETDATE(),@AVG,GETDATE(),'SERVICE')
					End*/
			End
		FETCH NEXT FROM employees_cursor INTO @filekey
	End

Close Employees_cursor

Deallocate Employees_cursor

Select * from @Results

Content Inventory

• Doc File: content/docs/utilities/write_rolling_6_week_avg_to_cf.mdx
• SQL Script: SQL/utilities/write_rolling_6_week_avg_to_cf.sql