Below is a query that displays a count on how many times individual people logged into an application within a specified time period. For instance, John Doe logged in 20 times since March 20. What I need to include though is the last date John Doe logged on. I was able to get a list of all 20 times but I'm really interested in just the last date he logged on.
SELECT tblUser.strUserID AS [USER ID], tblUser.strName AS NAME, COUNT(tblAudit.dtmTDStamp) AS [LOGIN COUNT] AS [LAST LOGIN DATE]
FROM tblUser INNER JOIN
tblAudit ON tblUser.strUserID = tblAudit.strUserID
WHERE (tblAudit.dtmTDStamp > CONVERT(DATETIME, '2009-03-20 00:00:00', 102))
GROUP BY tblUser.strUserID, tblUser.strName, tblAudit.bytRecType
HAVING (tblAudit.bytRecType = '1')
ORDER BY [USER ID]
Any suggestions will be appreciated.