Solved

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

Posted on 2013-07-01
7
341 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
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 26

Expert Comment

by:skullnobrains
Comment Utility
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
Comment Utility
@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 26

Accepted Solution

by:
skullnobrains earned 500 total points
Comment Utility
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
Comment Utility
Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to dynamically set the form action using jQuery.
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now