• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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

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
evibesmusic
Asked:
evibesmusic
  • 3
  • 2
  • 2
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
evibesmusicAuthor Commented:
@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
 
dsackerContract ERP Admin/ConsultantCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
skullnobrainsCommented:
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
 
evibesmusicAuthor Commented:
@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
 
skullnobrainsCommented:
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
 
evibesmusicAuthor Commented:
Thank you.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now