1 Reply Latest reply: Nov 7, 2011 9:53 AM by -==cfSearching==- RSS

    Query help

    wmkolcz Community Member
    wmkolcz Nov 6, 2011 7:26 PM

      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!!

      • 297 Views
      • Tags:
        • 1. Re: Query help
          -==cfSearching==- Community Member
          -==cfSearching==- Nov 7, 2011 9:53 AM (in response to wmkolcz)

          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#