Obtaining Latest Entry in Employee History Table

[Baggie]

Hi,
I'm relatively new to Crystal Reports. I have an issue I'm hoping someone can advise me on. I did search around on this site to try to find an answer but didn't see one that helped. So, first of all, I am on CR Version 12 SP 2. I work for a hospital HR department. I am trying to find the last instance of an employee termination date in the History table (employees come and go multiple times). I put together this query in MS Access and it works great. Would like to now put this in Crystal. I have the EMPLOYEE table and I select certain fields out of there then I create the other fields (the ones that start with "SELECT TOP 1...")via a subquery to the HISTORY table. I tried just copying the subquery statement into a SQL Expression field but get an error message about improper syntax. Thank you in advance for your help.

SELECT LAWPRD_EMPLOYEE.EMPLOYEE, LAWPRD_EMPLOYEE.LAST_NAME, LAWPRD_EMPLOYEE.FIRST_NAME, LAWPRD_EMPLOYEE.EMP_STATUS, LAWPRD_EMPLOYEE.TERM_DATE AS EmpTermDt, LAWPRD_EMPLOYEE.POSITION, (SELECT TOP 1 A_VALUE FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T2" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistCode1, (SELECT TOP 1 DATE_STAMP FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T2" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistDate, DateDiff("d",[TERM_DATE],[HRHistDate]) AS DaysSince, (SELECT TOP 1 USER_ID FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T2" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistUserID, (SELECT TOP 1 A_VALUE FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T1" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistCode2, (SELECT TOP 1 DATE_STAMP FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T1" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistDate2, DateDiff("d",[TERM_DATE],[HRHistDate2]) AS DaysSince2, (SELECT TOP 1 USER_ID FROM LAWPRD_HRHISTORY WHERE LAWPRD_HRHISTORY.EMPLOYEE = LAWPRD_EMPLOYEE.EMPLOYEE AND LAWPRD_HRHISTORY.A_VALUE="T1" ORDER BY LAWPRD_HRHISTORY.DATE_STAMP DESC) AS HRHistUserID2
FROM LAWPRD_EMPLOYEE
WHERE (((LAWPRD_EMPLOYEE.EMPLOYEE)=273) AND ((LAWPRD_EMPLOYEE.EMP_STATUS)="T2"));

 

[Charliy]

Set up a Parameter for Employee ID
Make your Record Select Formula {LAWPRD_EMPLOYEE.EMPLOYEE} ={?Employee_ID} AND {LAWPRD_EMPLOYEE.EMP_STATUS} = "T2"

Group by Employee, then sort by termination date

Suppress the Group Header and Detail and put everything in the Group Footer, since it's sorted by Termination Date you'll only see the latest information.

 

[Baggie]

Hi Charliy,
Thanks for your response. First thing I need to do is get the last termination date out of the History table. There can be multiple termination dates for some employees and I need to find the last entry (and it should match the term date in the Employee table). SO, I want to know if I can subquery in Crystal similar to the SQL I pasted in my original request. Hope that helps clarify.

 

[Charliy]

If the Employee table and the History table both have the Term date, then you can match on that.