Active Employees by Month

[shahzad]

Dears,

I want to calculate Active Employees by Month. In our organization every month new employees are joining and leaving. I need to know aur active employee count each month. 

I have the count of Active employees but this count shows as of now. How can I distribute in months.

Kindly check the sample data.

We have 2 tables Employee Table and Termination Table : When an employee is terminated, its record from Employee table is deleted and it is saved in Termination Table.

Emp Table:

Emp Id	Name	Date of Join	Status
001	EMP1	01-Jan-2024	Active
002	EMP2	05-Jan-2024	Active
004	EMP4	10-Feb-2024	Active
006	EMP6	11-Mar-2024	Active
007	EMP7	30-Apr-2024	Active
008	EMP8	15-May-2024	Active
009	EMP9	18-May-2024	Active
010	EMP10	23-May-2024	Active
011	EMP11	01-Jun-2024	Active

Termination Table:

Emp Id	Name	Join Date	Termination Date
003	EMP3	10-Jan-2024	20-Feb-2024
005	EMP5	05-Feb-2024	05-Mar-2024

Result Needed:

Month	Count of Employees
Jan	3 (EMP1,EMP2,EMP3)
Feb	4 (EMP1,EMP,2,EMP4,EMP5)
Mar	4 (EMP1, EMP2,EMP4,EMP6)
Apr	5 (EMP1, EMP2,EMP4,EMP6,EMP7)
May	8 (EMP1, EMP2,EMP4,EMP6,EMP7,EMP8,EMP9,EMP10)
Jun	9 (EMP1, EMP2,EMP4,EMP6,EMP7,EMP8,EMP9,EMP10,EMP11)

Let me know the solutions.

Regards

Shahzad

[Vegar · Accepted solution]

You can solve this using intervalmatch(), please review my sample script below using your sample data.

. 

SET DateFormat='DD-MMM-YYYY';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Periods_of_interest:
LOAD 
	Date, 
	Monthname(Date) as Year_Month;
LOAD 
	Dayname('01-Jan-2024', recno()-1) as Date
Autogenerate ('01-Jun-2024' - '01-Jan-2024')+1;


Emp_Table:
LOAD 
	[Emp Id],
	Name,
	[Date of Join],
	Status,
	today() as last_workday

inline [
	Emp Id	Name	Date of Join	Status
	001	EMP1	01-Jan-2024	Active
	002	EMP2	05-Jan-2024	Active
	004	EMP4	10-Feb-2024	Active
	006	EMP6	11-Mar-2024	Active
	007	EMP7	30-Apr-2024	Active
	008	EMP8	15-May-2024	Active
	009	EMP9	18-May-2024	Active
	010	EMP10	23-May-2024	Active
	011	EMP11	01-Jun-2024	Active
](Delimiter is '	');

Termination_Table:
Concatenate (Emp_Table)
LOAD 
	[Emp Id],
	Name,	
	[Join Date] as [Date of Join],
	[Termination Date],
	[Termination Date] as last_workday 
inline [
	Emp Id	Name	Join Date	Termination Date
	003	EMP3	10-Jan-2024	20-Feb-2024
	005	EMP5	05-Feb-2024	05-Mar-2024
](Delimiter is '	'); 


IntervalMatch (Date)
LOAD 
	[Date of Join],
	last_workday
Resident Emp_Table;

.

This can give you an output like in the table below. (The expressions used are shown in the header of the expression columns).

[shahzad]

Dear @Vegar 

Thanks for the solution. It is working