I need to create a query that returns the employee id and status code of all employees that fall within a certain point in time. For example I want to be able to tell which employees were Active Regular (AR) employees as of 6/30/06. Could someone please give me assistance on this?

I apologize if I posted this in the wrong area. I used the search function already and was unable to find anything related to my question above.

What tool are you using to create your query?

if you're using SQL then it would look something like this:

SELECT EMPLOYEE, EMP_STATUS FROM EMPLOYEE
WHERE DATE_HIRED > '06/30/2006' AND
AND EMP_STATUS = 'AR'

Is that what you need? If you're not an advanced user, the Excel Addins tool is a great way to start.

You'll also need to check your HR10 screen, to make sure status has been flagged for history. A REALLY simple tool, HR Writer, has an "as of" date when running the report on HR170 that provides point in time reporting, too. Good luck!

[QUOTE=trezaei,Feb 8 2007, 10:35 PM] What tool are you using to create your query?

if you're using SQL then it would look something like this:

SELECT EMPLOYEE, EMP_STATUS FROM EMPLOYEE
WHERE DATE_HIRED > '06/30/2006' AND
AND EMP_STATUS = 'AR'





I don't think that this will work because on 6/30/06 the status could be AR but at a later day say 1/1/07 the status could have changed to AP so it will return the AP and not the AR status.

Check out this thread:

http://lawsontalk.com/index.php?showtopic=952

I believe that the HRHISTORY field number for status is 15.

Make that 20.

You'll need to filter the records on status change date and date stamp from HRHISTORY so that you don't miss retroactive changes.