Hi, I am actually an HR consultant with minor experience in SQL, have asked our developers for help and received the query below, but it is not working - Please help!!!
My Client records training for employees, these employees need to be recertified at certain dates. I need a report between two dates, that only shows me the record with the most recent date in the field specified, using the script below I still get 2 records returned and not just one record with the later of the 2 dates.
USE database name
GO
SET NOCOUNT ON
SELECT T1.Sys_Prefix, T1.Company_No, T1.Emp_No, T1.Course_Typ, T1.Train_Code,
T1.Start_Date, T1.Cert_Date, T1.Cert_No, T1.Valid_Yrs, T1.Valid_Mnth,
T1.Recert_Dat,T1.Cert_Comm
FROM dbo.TNEmpCert AS T1
WHERE T1.Recert_Dat BETWEEN '<!$MG_From_Date>' AND '<!$MG_To_Date>'
/* Show Only The Last Record For The Employee. */
AND EXISTS (SELECT TOP 1 T1.Recert_Dat
FROM dbo.TNEmpCert AS T2
WHERE T2.Sys_Prefix = T1.Sys_Prefix
AND T2.Company_No = T1.Company_No
AND T2.Emp_No = T1.Emp_No
AND T2.Course_Typ = T1.Course_Typ
AND T2.Train_code = T1.Train_Code
ORDER BY T2.Recert_Dat DESC)
SET NOCOUNT OFF