Example Script: Using an Employee Table to Find Average Head Count Over Any Time Span

For some organizations it is essential to know, on an average basis, how many employees (or customers, etc.) existed over any period. If one is calculating the productivity or usage of a given organization of a resource, especially if the number of employees is changing, then being able to precisely calculate, on a weighted basis, how many existed is key. Unfortunately a typical Employee table just contains a list of employees and dates of hire. What is needed is a table that, by day, has the total number of employees at that time. Then, one can average that measure over any given period. Assume that there is a source called “Person” that contains one record for every employee. That table can then be used to generate a running total, by Hire Date, of how many employees exist. So, first we create a “Running Hires” source based on this Person source.

Select Statement:

Select [Person.PK_CLIENT],[Person.LEVEL1],[Person.HIRE_DATE],[Person.EXPIRED] from [Person] Order By [Person.LEVEL1],[Person.HIRE_DATE]

The script simply keeps track based on organization level (assuming this needs to be done by organization) of the total population. Note that CREATED is used as the date in common.

Script:

Dim [Level1] As Varchar(40) = ''

Dim [Hired] As Integer = 0

If [Level1]<>[Person.LEVEL1]

[Hired] = 0

[Level1]=[Person.LEVEL1]

End If

[Hired] = [Hired] + 1

[CREATED]=[Person.HIRE_DATE]

[PK_CLIENT]=[Person.PK_CLIENT]

[LEVEL1]=[Person.LEVEL1]

[Running Hired] = [Hired]

WriteRecord

Next, we need to fill in missing days so that there is an entry for every day. We create a new source called “Filled Hires”:

SELECT [Running Hires.PK_CLIENT], [Running Hires.CREATED], [Running Hires.Running Hired], [Running Hires.LEVEL1] FROM [Running Hires]

Dim [LastLevel1] As Varchar(40) = ''

Dim [LastCreated] As DateTime

// Get first and last dates from some key table

Dim [MinDate] As DateTime = Select Min([Awards.CREATED]) From [KeyTable]

Dim [MaxDate] As DateTime = Select Max([Awards.CREATED]) From [KeyTable]

// If this is a new department, fill out the dates until the last day

If [LastLevel1] <> [Running Hires.LEVEL1] Then

While [LastCreated]<[MaxDate]

[CREATED]=[LastCreated]

WRITERECORD

[LastCreated] = DateAdd(Day,1,[LastCreated])

End While

[LastLevel1]=[Running Hires.LEVEL1]

[LastCreated] = [MinDate]

End If

[Running Hired]=[Running Hires.Running Hired]

[LEVEL1]=[Running Hires.LEVEL1]

[PK_CLIENT]=[Running Hires.PK_CLIENT]

// Fill in any days from the last hire until this one

While [LastCreated]<[Running Hires.CREATED] AND [Running Hires.CREATED] Is Not Null

[CREATED]=[LastCreated]

WRITERECORD

[LastCreated] = DateAdd(Day,1,[LastCreated])

End While

// When done, fill the last departments days

Complete

While [LastCreated]<[MaxDate]

[CREATED]=[LastCreated]

WRITERECORD

[LastCreated] = DateAdd(Day,1,[LastCreated])

End While

End Complete

This script can then be combined with a similar “Filled Expires” to get a running count based on the cumulative hired and expired employees:

SELECT [Filled Hires.PK_CLIENT], [Filled Hires.CREATED], [Filled Hires.Running Hired], [Filled Hires.LEVEL1] FROM [Filled Hires]

INNER JOIN

SELECT [Filled Expires.PK_CLIENT], [Filled Expires.CREATED], [Filled Expires.Running Expired], [Filled Expires.LEVEL1] FROM [Filled Expires] On

[Filled Hires.PK_CLIENT]=[Filled Expires.PK_CLIENT] And [Filled Hires.CREATED]=[Filled Expires.CREATED] And [Filled Hires.LEVEL1]=[Filled Expires.LEVEL1]

Script to simply join the two sets together:

[PK_CLIENT]=[Filled Hires.PK_CLIENT]

[CREATED]=[Filled Hires.CREATED]

[Running Hired]=[Filled Hires.Running Hired]

[Running Expired]=[Filled Expires.Running Expired]

[LEVEL1]=[Filled Hires.LEVEL1]

WRITERECORD

Now if you target this table correctly, you can simply take an average of the “Running Hired-Running Expired” measure to get the average number of employees over any stretch of time.