?
Solved

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

Posted on 2013-07-01
7
Medium Priority
?
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
WordPress Tutorial 1: Installation & Setup

WordPress is a very popular option for running your web site and can be used to get your content online quickly for the world to see. This guide will walk you through installing the WordPress server software and the initial setup process.

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

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

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

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…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

771 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