Solved

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

Posted on 2013-07-01
7
347 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 26

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 26

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP Parse error: syntax error, unexpected '[' 1 23
SQL Server Count where two id types exist in column 8 27
mysql date time 14 28
VB.net and sql server 4 33
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
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 …

786 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