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