Need to identify Max Date and rest of data on row

[HRISmess]

I am in the process of doing some point in time reporting and am having difficulty identifying all the data on row 'As of' a specific date.

For instance, I need to identify an Employee's status As of a specific date.

The query below identifies the maximum effective date and corresponding data on the row...how can add a WHERE clause to look for the 'As Of' date?

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

 

[lespaul]

can you provide some sample data and expected results from the tables PS_JOB and JOB?

thanks,
leslie

 

[HRISmess]

EMPLID EFFDT EMPL_STATUS JOBCODE

1111 1/1/05 A BAS
1111 1/1/04 L AOK
1111 1/1/06 A SAD

Right now my query identifies the last row as the maximum effective date.

What I am trying to answer is a a question such as: Identify the following information: EMPLID, EMPL_STATUS, JOBCODE on say...6/1/2005.

 

[lynchg]

Try adding a WHERE clause with:

Where AsOfDate > A.EffDt

I am not sure if the query will perform the max() before looking at the where clause or not. You may have to first restrict ouptut to those dates less than your 'as of' date, and then take the max date value from that set of data.

 

[HRISmess]

I appreciate the feedback. I wonder if adding the Where clause will limit output to only those with a Maximum effective Date than the 6/1/2005 example cited above?

I'll try it and see where that gets me.

 

[HRISmess]

bump