Query help

Question

I need help with a query and wanted to see if anyone here could help.

Lets say I have a table with 'username' and 'lastadded' (datetime) a which each user could have mulitiple records. I wanted to see if the persons last added record (lastadded) was over a month ago.

First I need to pull the last record for each person in the table then say => one month from today. The date compare is pretty easy but i have no idea on how to pull just the top record of each person in the table.

To give you the scenerio, I am building a volunteer manager and one section is a retention module. The users want to see if a voluteers last login is over a month old and only pull those peoplei into a report. So I need to pull the last time they logged in then compare dates.

Not sure if this would be one or two queries. Thanks for any help you all can provide!!

-__cfSearching__- · Answer

The date compare is pretty easy but i have no idea on how to pull just
the top record of each person in the table.

Use MAX to get the last login date by username. Then wrap it up as a derived table so you can filter out the max dates that are one month ago or more.

Note: Replying via email so this may get mangled...

SELECT u.UserName, u.LastLogin

FROM (

SELECT UserName, MAX(lastAdded) AS LastLogin

FROM YourTable

GROUP BY UserName

) u

WHERE u.LastLogin <= #dateOneMonthAgo#

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded