Hi
Something like this
// set start date of headcount table
Let vStartDate = Date#('2013/01/01');
// set monthly range for headcount table
T_Data:
LOAD PeriodID,
Date(AddMonths($(vStartDate), PeriodID - 1), 'YYYY-MM') As MonthYr
;
LOAD RecNo() As PeriodID
AutoNumber 24;
//Load employee data
Employees:
LOAD grade,
empno,
empname,
MonthStart(doj) As StartDate,
MonthStart(date_of_resign) As EndDate
From .....
// Join the tables
Join (T_Data)
LOAD * Resident Employees;
// Identify active employees
T_Data2:
LOAD grade,
PeriodID,
MonthYr,
empno,
If(StartDate < MonthYr And MonthYr <= EndDate, 1, 0) As Active
Resident T_Data;
//Construct headcount table
HeadCount:
LOAD *,
GrossAdditions - Attrition As NetAdditions,
OpeningBalance + GrossAdditions - Attrition As ClosingBalance
;
LOAD PeriodID,
MonthYr,
grade,
Count(Distinct If(Active = 1, empno)) As OpeningBalance,
Count(Distinct If(MonthStart(StartDate) = MonthYr) empno) As GrossAdditions,
Count(Distinct If(Active = 1 And EndDate = MonthYr, empno)) As Attrition
Resident T_Data2\
Group By PeriodID, MonthYr, grade
;
//Cleanup
DROP Table T_Data, T_Data2;
(check for syntax errors like missing commas etc)
HTH
Jonathan
Edit - added missing Group By...
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
