NewCor Peak Employee Billing Report

This specialized script generates peak employee counts by location for NewCor billing purposes, providing both employee headcount and ESS (Employee Self Service) usage metrics organized by location-based workgroups.

Overview

The NewCor Peak Employee Billing Report is designed specifically for billing calculations and client reporting. It identifies the peak employment date within a specified period and provides detailed breakdowns by location (WG1 workgroups), including both total employee counts and ESS system usage for accurate billing computations.

Parameters

@STARTDATE - Beginning of billing period (currently set to '4/1/2023')
@ENDDATE - End of billing period (currently set to '4/30/2023')
Note: Parameterized versions are commented out ({mindate}, {maxdate})

Data Components

The report consists of several billing-focused components:

Peak Date Identification - Finds the highest employee count date for billing
Employee Data Parsing - Extracts all active employees from DAILYSTATE
Location Assignment - Associates employees with WG1 (location) workgroups
ESS Usage Calculation - Counts ESS users per location for service billing
Billing Summary - Location-based employee and service counts

Output Format

Column	Description
WGNUM	Workgroup number (location) or null for headers
WGNAME	Location name or billing summary information
EECOUNT	Employee count per location for billing
ESSCOUNT	ESS user count per location for service billing

Technical Implementation

The script uses:

WORKGROUP1 table for location-based billing organization
DAILYSTATE parsing for accurate peak date employee extraction
EMPLOYEEACTIVITY integration for ESS service usage tracking
Location-specific filtering to show only active locations
Billing-optimized data structure for client reporting
Peak date calculation for consistent billing periods

Example Usage

Data Dependencies

DAILYSTATE table for peak employee date calculation
EMPLOYEES table for employee details and location assignments
WORKGROUP1 table for location-based billing structure
EMPLOYEEACTIVITY table for ESS service usage tracking
EMPHOMEWGHIST table for historical location assignments

Billing Considerations

Peak Date Methodology: Uses highest employee count date in period
Location-Based Billing: Organized by WG1 (physical locations)
Service Metrics: Includes ESS usage for service-based billing
Active Locations Only: Filters out locations with zero employees

Business Value

This report helps organizations:

Generate accurate billing reports for NewCor clients
Track location-specific employee counts for billing purposes
Monitor ESS service usage for service-based billing models
Provide transparent billing documentation to clients
Support contract compliance and billing verification
Maintain consistent billing methodologies across periods

T-SQL

newcor_peakemp_billing_report.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @EMPLOYEES TABLE (FK INT, ACTIVEWORKGROUP INT)
DECLARE @ESSINFO TABLE (ESSCOUNT INT, WG int)  
DECLARE @WGINFO TABLE(WGNUM INT,WGNAME VARCHAR(300),EECOUNT INT, ESSCOUNT INT) 

DECLARE
  @ITERATION SMALLINT,
  @COUNTER SMALLINT,
  @LENGTH SMALLINT,
  @CURRENTCHARACTER VARCHAR(1),
  @CURRENTVALUE VARCHAR(16),
  @INDEX SMALLINT,
  @STARTVALUE SMALLINT,
  @ENDVALUE SMALLINT,
  @START DATETIME,
  @END DATETIME,
  @ITEMTYPE SMALLINT,
  @STARTDATE DATETIME,
  @ENDDATE DATETIME,
  @EECOUNT INT,
  @HIGHDATE INT

SET @STARTDATE = '4/1/2023'
set @enddate = '4/30/2023'

/*
SET @STARTDATE ={mindate}
set @enddate = {maxdate}
*/
SET @ITERATION=1

WHILE (@ITERATION<=1)
BEGIN
IF @ITERATION=1
--************************************************************************************************************************************************
--Sets @Start and @END to the top (newest) EventDate inside of the search range. for example search range starts 4/1 and today is 4/13 is will come back with 4/13
--************************************************************************************************************************************************
BEGIN
	SET @ITEMTYPE=0
		SELECT TOP 1 
			@START = EVENTDATE
         FROM DAILYSTATE 
         WHERE (EVENTDATE >= @STARTDATE AND EVENTDATE <= @ENDDATE) AND ITEMTYPE = @ITEMTYPE
         GROUP BY EVENTDATE, INSTANCES
         ORDER BY INSTANCES DESC, EVENTDATE DESC
     SET @END = @START
END


--************************************************************************************************************************************************
--           Farse the filekey for the day out of REFDATA
--************************************************************************************************************************************************
  WHILE (@START <= @END)
      BEGIN
        SET @COUNTER = 0
        SELECT 
          @LENGTH = DATALENGTH(REFDATA) -- RefDATA appears to be a list of filekeys for the day.)
          FROM DAILYSTATE 
          WHERE EVENTDATE = @START AND ITEMTYPE = @ITEMTYPE
          SET @CURRENTVALUE = ''

        WHILE (@COUNTER <= @LENGTH)
          BEGIN
            SELECT @CURRENTCHARACTER = SUBSTRING(REFDATA, @COUNTER, 1) 
              FROM DAILYSTATE 
              WHERE EVENTDATE = @START AND ITEMTYPE = @ITEMTYPE
    
            IF @CURRENTCHARACTER != ',' SET @CURRENTVALUE = @CURRENTVALUE + @CURRENTCHARACTER

IF @CURRENTCHARACTER = ',' OR @COUNTER = @LENGTH
              BEGIN
                SET @INDEX = CHARINDEX(':', @CURRENTVALUE)
               
                IF @INDEX = 0 OR @INDEX IS NULL 
                  BEGIN
                    IF @ITEMTYPE = 0
                      INSERT INTO @EMPLOYEES VALUES (CONVERT(SMALLINT,@CURRENTVALUE), 0)
                    
               
                  END
        
 IF @INDEX > 0
                  BEGIN
                    SET @STARTVALUE = CONVERT(SMALLINT,LEFT(@CURRENTVALUE,@INDEX-1))
                    SET @ENDVALUE = CONVERT(SMALLINT,RIGHT(@CURRENTVALUE,LEN(@CURRENTVALUE)-@INDEX))
                                       
                    WHILE (@STARTVALUE <= @ENDVALUE)
                      BEGIN
                        IF @ITEMTYPE = 0
                          INSERT INTO @EMPLOYEES VALUES (@STARTVALUE, 0)
                       
             
                        SET @STARTVALUE = @STARTVALUE + 1
                      END
                  END
                
                IF @CURRENTCHARACTER = ',' SET @CURRENTVALUE = ''
              END
            SET @COUNTER = @COUNTER + 1
          END
        SET @START = DATEADD(DAY,1,@START)
      END      
    SET @ITERATION = @ITERATION + 1
  END
SET @START = DATEADD(DAY,-1,@START)


  
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP=(SELECT WG1 FROM EMPLOYEES WHERE FILEKEY=FK AND WGEFFDATE<=@START)
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP=(SELECT TOP 1 WG1 FROM EMPHOMEWGHIST WHERE FILEKEY=FK AND EFFDATE<=@START ORDER BY EFFDATE DESC)WHERE ACTIVEWORKGROUP IS NULL
UPDATE @EMPLOYEES SET ACTIVEWORKGROUP= (SELECT WG1 FROM EMPLOYEES WHERE FILEKEY=FK)WHERE ACTIVEWORKGROUP IS NULL

INSERT INTO @ESSINFO Select COUNT(DISTINCT EA.FILEKEY), e.ACTIVEWORKGROUP 
						FROM EMPLOYEEACTIVITY EA 
						Inner join @EMPLOYEES e on EA.filekey = e.FK
						WHERE EA.FILEKEY IN (SELECT fk FROM @EMPLOYEES) 
						group by e.ACTIVEWORKGROUP

INSERT INTO @WGINFO SELECT NULL,'Active Employee Count Per Location '+DATENAME(MONTH,@START)+' '+DATENAME(YEAR,@START),NULL,NULL

INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL,'Peak Employee Date'+' '+CONVERT(VARCHAR(10),@START,101),NULL,NULL
INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT DISTINCT WGNUM,NAME,NULL,NULL FROM WORKGROUP1 ORDER BY 2

UPDATE @WGINFO SET EECOUNT=(SELECT COUNT(*) FROM @EMPLOYEES WHERE ACTIVEWORKGROUP=WGNUM)WHERE WGNUM IS NOT NULL
UPDATE @WGINFO SET ESSCOUNT=(Select es.ESSCOUNT FROM @ESSINFO es WHERE es.WG=WGNUM)WHERE WGNUM IS NOT NULL 

INSERT INTO @WGINFO SELECT NULL,NULL,NULL,NULL
INSERT INTO @WGINFO SELECT NULL, 'TOTAL',(SELECT SUM(EECOUNT) FROM @WGINFO),(Select SUM(ESSCOUNT) from @WGINFO)

SELECT * FROM @WGINFO where (EECOUNT!=0 or EECOUNT IS NULL)

Content Inventory

Doc File: content/docs/reports/system_information/newcor_peakemp_billing_report.mdx
SQL Script: SQL/reports/system_information/newcor_peakemp_billing_report.sql

NewCor Peak Employee Billing Report

On this page