In a bad way - Need help with Max Date

[HRISmess]

The query below identifies the last day an employee is in an EMPL_STATUS of (A or L). I need to now count all employees that were active as of monthend for each month of 2006...and I am stuck!

Do I run a separate query to identify all EEs who have termed and their effective date; then, compare to effective date of the query below?

SELECT A.EMPLID, A.EFFDT, A.EFFSEQ, A.EMPL_STATUS, A.JOBCODE, A.DEPTID
FROM JOB AS A INNER JOIN [SELECT EMPLID, Max(EFFSEQ) As LastSeq, Max(EffDt) As LastDate FROM JOB GROUP BY EMPLID
]. AS L ON (A.EFFDT = L.LastDate) AND (A.EMPLID = L.EMPLID)
WHERE (((A.EMPL_STATUS) In ("A","L")) AND ((A.DEPTID) Like "######10*"))
ORDER BY A.EMPLID;

 

[SkipVought]

Hi,

try this

SELECT A.EMPLID, A.EFFDT, A.EFFSEQ, A.EMPL_STATUS, A.JOBCODE, A.DEPTID

FROM JOB AS A INNER JOIN [SELECT EMPLID, Max(EFFSEQ) As LastSeq, Max(EffDt) As LastDate FROM JOB GROUP BY EMPLID
]. AS L ON (A.EFFDT = L.LastDate) AND (A.EMPLID = L.EMPLID)

WHERE (((A.EMPL_STATUS) In ("A","L")) 
  AND ((A.DEPTID) Like "######10*"))[b]
  and A.EFFDT = DateSerial(year(A.EFFDT), month(A.EFFDT),0)
[/b]
ORDER BY A.EMPLID;

Skip,

_[red][/red]