MAX updated row per User record

use AssessmentApp

declare @LatestDateTime as datetime

SET @LatestDateTime = '09/12/2014 04:03:15.000'

-- this will capture EVERY record.

/*

SELECT ASSESSMENT_ID, AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table]

WHERE AUDIT_DATE_TIME <= @LatestDateTime

ORDER BY AUDIT_DATE_TIME DESC

*/

-- this will capture the LATEST record for each Assessment_ID.

SELECT a.ASSESSMENT_ID, a.AUDIT_DATE_TIME, AUDIT_BY_PPID, AUDIT_BY_NAME, NEW_ASSESSMENT_STATUS, ASSESSMENT_STATUS, USER_AGENT_TYPE, USER_AGENT_VERSION

FROM [dbo].[table] a

WHERE AUDIT_DATE_TIME IN (

                          SELECT MAX(aa.AUDIT_DATE_TIME)

                          FROM [dbo].[ASSESSMENT_AUDIT] aa

                          WHERE aa.AUDIT_DATE_TIME <= @LatestDateTime

                             and aa.Assessment_ID = a.ASSESSMENT_ID                                                    

                            GROUP BY aa.ASSESSMENT_ID

                                         )

       AND AUDIT_DATE_TIME <= @LatestDateTime

--     AND assessment_id = 88205

ORDER BY a.Audit_Date_Time