Select only the most recent date from records

[jadiebabe]

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

[AntonioGK]

Hello,

You have to change the EXISTS to =

I wrote a similar example using NorthWind Database

SELECT     CustomerID, ShipCountry, ShipPostalCode, RequiredDate
FROM         Orders a
WHERE
     (

       RequiredDate =( SELECT     TOP 1 RequiredDate
                                FROM        orders AS b
                                WHERE      a.customerID = b.customerID
                                         AND a.shipCountry = b.shipCountry
                                         AND a.shipPostalCode = b.shipPostalcode
                                 ORDER BY 'DESC'
                              )
     )
        AND (RequiredDate 
                             BETWEEN
                     CONVERT(DATETIME, '1997-09-22 00:00:00', 102)
                            AND 
                     CONVERT(DATETIME, '2008-01-01 00:00:00', 102)
  )

ORDER BY RequiredDate DESC

Hope this help you.

[DemonBob]

Sorry to necro-post, but this just saved me tons of work! Still valid and very useful.