Calculate Years employed

[lamont]

Please help with the following:

I have an employee history table with the following columns:
EMPLID, EFFDT, ACTION

As an example:
1234, 12/31/2004, HIR --as an employee's hire row

This table keeps the history of different HR transactions against employees.
So it can have mulitiple rehire and termination rows for the same
emplid.

Using a select statement, is there a way to pull total years
employed...accounting for those who leave and come back too?

Example:

EMPLID, EFFDT, ACTION
0013, 10/01/1979, HIR
0013, 09/10/1985, TER
0013, 09/25/1990, REH
0013, 01/30/2000, TER
0013, 07/25/2003, REH

How do I get the total years employed using a select statement or query from
the above example?

Thanks a ton!

 

[Allen Browne]

Use a subquery to get the matching termination record for the same employee.

Something like this:

SELECT EmployHistory.*,
DateDiff("yyyy", EmployHistory.EffDt,
Nz((SELECT Min(EffDt) FROM EmployHistory AS Dupe
WHERE ((Dupe.Action = 'Ter')
AND (Dupe.EmplId = EmployHistory.EmpId)
AND (Dupe.EffDt > EmployHistory.EffDt))), Date())) AS Years
FROM EmployHistory
WHERE EmployHistory.Action IN ('HIR', 'REH');

Notes:
1. This assumes there is a termination record for *every* starting record,
except for those that are still current.

2. The expression just gives the difference between the years of the dates.
Therefore Dec 31 2004 and Jan 1 2005 would yield 1 year, and so would Jan 1
2004 to Dec 31 2005. If you need something more than that, there's a
discussion about how to calculate it here:
http://allenbrowne.com/func-08.html

3. If any dates are blank, the query assumes today's date.

4. Records are ignored where the Action is other than Hir, Reh, or Ter
(including blank actions).

5. The situation will be more complex if an employee could be part-time
employeed by different departements simultaneously.

6. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066