• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

SQL query to include last date a person used an application

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.
0
vlay
Asked:
vlay
1 Solution
 
Patrick MatthewsCommented:
Just add MAX(NameOfLoginDatetime) to your select clause (changing to the right column name first!).
0
 
vlayAuthor Commented:
THANK YOU!  
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now