Solved

How to find out which members have not logged-in within the past year?

Posted on 2013-07-01
7
352 Views
Last Modified: 2013-07-03
Experts,

I am trying to clean up our membership database.

I have two tables. A membership table called 'members' which houses all membership information, and a logins table called "logins" which tracks each successful log-in.

I'd like to be able to join the two tables and find the member records for those users that have not logged in within the last calendar year.

Note: The 'LoginDate' field is a current_timestamp field.

SELECT *.members FROM members JOIN logins ON members.NUID=logins.NUID WHERE logins.LoginDate > OneYear AND logins.LoginDate !< OneYear

I appreciate your help.

Cheers!
0
Comment
Question by:evibesmusic
  • 3
  • 2
  • 2
7 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39291866
All you probably care about is the MAX LoginDate (the last LoginDate) being older than a year ago:

SELECT *.members
FROM members
JOIN (SELECT NUID, MAX(LoginDate) AS MaxDate FROM logins
      GROUP BY NUID) t1
ON members.NUID=t1.NUID
WHERE logins.MaxDate < DATEADD(y, -1, GETDATE())

Open in new window

0
 

Author Comment

by:evibesmusic
ID: 39291896
@dsacker:

Yes, I guess all I do care about is comparing someone's MaxDate.

So if their MaxDate is not within a year's time, then I want to show this person's name.

SELECT DISTINCT members.FirstName, members.LastName
FROM members
JOIN (SELECT NUID, MAX( LoginDate ) AS MaxDate FROM logins GROUP BY NUID) logins
ON members.NUID = logins.NUID
WHERE logins.MaxDate < date_add( curdate( ) , INTERVAL -1year );

Changed your query up a bit and this now works but, I am not quite sure about the result. This query only produces one record from the 'members' table...which means that only one person has not logged-in in the past year?

Somewhat skeptical about that?
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39291901
Maybe turn the query around some. Run it with and without the HAVING, to do a little data mining.

SELECT  logins.NUID,
        members.FirstName,
        members.LastName,
        MAX(logins.LoginDate) AS MaxDate
FROM   logins
LEFT JOIN members
ON members.NUID = logins.NUID
GROUP BY logins.NUID,
        members.FirstName,
        members.LastName
HAVING MAX(LoginDate) < DATE_ADD(curdate(), INTERVAL -1 year)

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 27

Expert Comment

by:skullnobrains
ID: 39292201
or rather

SELECT
        members.FirstName,
        members.LastName,
        MAX(logins.LoginDate) AS MaxDate
FROM   logins
INNER JOIN members
ON members.NUID = logins.NUID
GROUP BY logins.NUID,
HAVING MaxDate < DATE_ADD(curdate(), INTERVAL -1 year)

or maybe

SELECT
        members.FirstName,
        members.LastName,
FROM   logins
LEFT JOIN (SELECT NUID, LoginDate FROM logins where LoginDate > DATE_ADD(curdate(), INTERVAL -1 year GROUP BY NUID)
ON members.NUID = logins.NUID
where logins.LoginDate is null

the first one should be much faster
0
 

Author Comment

by:evibesmusic
ID: 39295610
@All:

I just realized that I made this more complicated than it needs to be.

The FirstName and LastName are also stored in the 'logins' table. My bad.

The query below is wrong and I know this. I'm not quite understanding how to group the MaxDate by NUID? The reason to do this is because there may be the same NUID in the table 20 times, and I only want the MaxDate of each unique NUID.

SELECT logins.FirstName, logins.LastName, logins.LoginDate FROM logins WHERE (SELECT MAX(logins.LoginDate) AS MaxDate FROM logins) NOT BETWEEN curdate() AND DATE_ADD(curdate(), INTERVAL -1 year)
0
 
LVL 27

Accepted Solution

by:
skullnobrains earned 500 total points
ID: 39296845
I'm not quite understanding how to group the MaxDate by NUID? The reason to do this is because there may be the same NUID in the table 20 times, and I only want the MaxDate of each unique NUID.

select max(LoginDate)
from logins
group by NUID

this will return the greater value in the logindate for each existing NUID

and if you want the complete information

select NUID,max(LoginDate) as lastLogin
from logins
group by NUID
having lastLogin < DATE_ADD(curdate(), INTERVAL -1 year)
0
 

Author Closing Comment

by:evibesmusic
ID: 39297781
Thank you.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question