Average Hours Report

Report that calculates average weekly hours worked for employees over a specified date range, determining the number of weeks each employee worked and computing their average hours per week

Parameters

@start - Start date for the analysis period (example: '1/1/2020')
@end - End date for the analysis period (example: '12/31/2020')

Note: The script includes placeholder parameters {mindate} and {maxdate} for integration with reporting tools.

Data Components

The report consists of 4 main sections:

Employee Information - Basic employee identification and department
Position and Status - Employee position and active/terminated status
Work Period Analysis - Number of weeks worked during the period
Hours Calculation - Total hours worked and average hours per week

Average Hours Report

💡 Click the image above to zoom in and see the calendar details more clearly!

Technical Implementation

The script uses:

Date alignment - Adjusts start/end dates to Sunday-Saturday week boundaries
Table variables - @EELIST, @HW, and @RPT for data processing stages
WHILE loops - For date adjustment and employee processing
Weekly calculations - Processes data in 7-day increments
Pay designation filtering - Includes only pay designations 1 and 2 (regular and overtime)
Workgroup joins - Links to WORKGROUP3 (department) and WORKGROUP4 (position)
Decimal precision - Uses DECIMAL(38,2) for accurate hour calculations
Conditional aggregation - Only counts weeks with recorded hours

T-SQL

avg_hours_report.sql

SET NOCOUNT ON

DECLARE
  @start DATETIME,
  @end DATETIME,
  @filekey INT,
  @idnum VARCHAR(20),
  @name VARCHAR(70),
  @department VARCHAR(32),
  @weeksworked SMALLINT,
  @weeksover20 SMALLINT,
  @hoursworked REAL,
  @startcounter DATETIME,
  @endcounter DATETIME,
  @position varchar(32),
  @status varchar(32)
  
DECLARE @EELIST table (FILEKEY INT, IDNUM VARCHAR(20), NAME VARCHAR(70), DEPARTMENT VARCHAR(32), POSITION VARCHAR(32), Status varchar(30)) 
DECLARE @HW TABLE (FILEKEY INT, Hours Decimal(38,2))
DECLARE @RPT TABLE (IDNUM VARCHAR(20), NAME VARCHAR(70), DEPARTMENT VARCHAR(32), Position VARCHAR(32), Status VARCHAR(32), WEEKSWORKED SMALLINT, HoursWorked Decimal(38,2), Average Decimal(38,2))

/*
SET @start = {mindate}
SET @end = {maxdate}
*/

SET @start = '1/1/2020'
SET @end = '12/31/2020'


WHILE (DATENAME(DW,@start) != 'Sunday')
  BEGIN
    SET @start = DATEADD(d,-1,@start)
  END
  
WHILE (DATENAME(DW,@end) != 'Saturday')
  BEGIN
    SET @end = DATEADD(d,1,@end)
  END


INSERT INTO @EELIST
  SELECT
    EE.FILEKEY,
    EE.IDNUM,
    EE.LASTNAME + ', ' + EE.FIRSTNAME,
    WG.NAME,
    WG4.NAME,
    EE.ACTIVESTATUS
  FROM
    EMPLOYEES EE
    JOIN WORKGROUP3 WG ON EE.WG3 = WG.WGNUM
    JOIN WORKGROUP4 WG4 ON EE.WG4 = WG4.WGNUM

INSERT INTO @HW
	Select 
		paydeshist.filekey,
		Sum(Hours) 
	from PAYDESHIST 
		join @EELIST ee on 
		ee.FILEKEY=PAYDESHIST.FILEKEY  
	where (EVENTDATE>=@start and EVENTDATE<=@end) and PAYDESNUM in (1,2) group by paydeshist.FILEKEY


WHILE (SELECT COUNT(*) FROM @EELIST) > 0
  BEGIN

   SET @startcounter = @start
    SET @endcounter = DATEADD(d,6,@start)
    SET @hoursworked = 0
    SET @weeksworked = 0
    SET @weeksover20 = 0


   SELECT TOP 1
      @filekey = FILEKEY,
      @idnum = IDNUM,
      @name = NAME,
      @department = DEPARTMENT,
      @position=POSITION,
      @status=Status      
    FROM
      @EELIST

   WHILE (@endcounter <= @end)
      BEGIN
      
        SELECT
          @hoursworked = SUM(HOURS)
        FROM
          PAYDESHIST
        WHERE
          FILEKEY = @filekey AND
          (EVENTDATE >= @startcounter AND EVENTDATE <= @endcounter)  and PAYDESNUM in (1,2)
      
        IF (@hoursworked > 0)
          SET @weeksworked = @weeksworked + 1
  
 
        SET @startcounter = DATEADD(d,7,@startcounter)
        SET @endcounter = DATEADD(d,6,@startcounter)
      
      END
      
      INSERT INTO @RPT VALUES (
        @idnum,
        @name,
        @department,
        @position,
        @status,
        @weeksworked,
       (select Hours from @HW where FILEKEY=@filekey),null)

    DELETE FROM @EELIST WHERE FILEKEY = @filekey

 END
     
select 
	IDNUM,
	NAME,
	DEPARTMENT,
	position,
	case when status=0 then 'Active' else 'Terminated' end As Status,
	WEEKSWORKED,
	cast (HoursWorked as decimal(38,2)) as WorkedHrs,
	cast((HoursWorked/WEEKSWORKED)as decimal(38,2) ) as 'AVG'

from @RPT where weeksworked>0 ORDER BY NAME

Content Inventory

Doc File: content/docs/reports/employee_information/avg_hours_report.mdx
SQL Script: SQL/reports/employee_information/avg_hours_report.sql
Screenshot: public/img/screenshots/reports/employee_information/avg_hours_report.png

Average Hours Report

Parameters

Data Components

Technical Implementation

T-SQL

Content Inventory

On this page