Users not logged in for more than 1 year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Users not logged in for more than 1 year

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-15 : 10:26:52

Hi All

I am trying to generate a list of users who have NOT logged in within the last year and then of those show the latest date they did login.

i have a table of users e.g.


UserID     Name
1          Paul
2          John

and a table of their login dates e.g.


UserID     LoginDate
1          22/04/2012
1          25/09/2011
1          03/03/2011
2          27/02/2011
2          20/03/2011

Based on the above data i would only see the following for users who have not logged in within the last year and display the last date (if any) they did...


UserID      Name     LoginDate
2           John     20/03/2011

Any ideas how to go about this greatly appreciated

====
Paul

Sandips
Starting Member

5 Posts

Posted - 2012-06-15 : 10:45:32

Please let me know if this helps :

DECLARE @TBL_LOGIN TABLE
(
UserID INT,
LOGINDATE DATE
)

INSERT INTO @TBL_LOGIN
(
UserID ,
LOGINDATE
)
VALUES
(1, '2012/04/22'),
(1, '2011/09/25'),
(1, '2011/03/03'),
(2, '2011/02/27'),
(2, '2011/03/20')

SELECT *
FROM @TBL_LOGIN
WHERE LOGINDATE < CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE)

“Normalize ’till it hurts, then denormalize
’till it works.”

Sandips
Starting Member

5 Posts

Posted - 2012-06-15 : 10:48:56

I have modified the query to show the latest date they login
DECLARE @TBL_LOGIN TABLE
(
UserID INT,
LOGINDATE DATE
)

INSERT INTO @TBL_LOGIN
(
UserID ,
LOGINDATE
)
VALUES
(1, '2012/04/22'),
(1, '2011/09/25'),
(1, '2011/03/03'),
(2, '2011/02/27'),
(2, '2011/03/20')

SELECT UserID,
MAX(LOGINDATE)
FROM @TBL_LOGIN
WHERE LOGINDATE < CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE)
GROUP BY UserID

“Normalize ’till it hurts, then denormalize
’till it works.”

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-15 : 11:01:13

Thanks for the quick reply

i had something like this already...

userLoginDate <= dateadd(month, -12, getdate())

this does show users max logindate if it is older than 1 year but i also need to know that they haven't logged in for a year

so i think with
CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE)

i would get the following results


uerid     logindate
1         2011/03/03
2         2011/03/20

i would not want userid #1 though as they have logged on within the last year

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-15 : 12:05:00

i'm thinking something like this will probably work for me...


Select
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	MAX(ULO.userLoginDate) AS userLoginDate
from
	dbo.tbl_Users US
left join
	dbo.tbl_UserLogins AS ULO ON ULO.userLogin_userID = US.userID
WHERE 
	userLogin_magJournalCode = 'GP'
AND
	US.userID NOT IN 
	(
		SELECT DISTINCT
			U.userID
		FROM 
			dbo.tbl_Users U
		left join
			dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID
		WHERE
			UL.userLoginDate >= dateadd(month, -12, getdate())
	)
GROUP BY
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	ULO.userLoginDate

if anyone can think of a faster way (this takes a millenium to run due to millions of recs) then please let me know.

hmmmm.... whilst it pulls out the correct logins it doesn't return the MAX login of the user

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 04:59:35

problem solved.

Had the login date in the group by clause.


Select
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail,
	--ULO.userLoginDate
	MAX(cast(ULO.userLoginDate as datetime)) AS userLoginDate
	
from
	dbo.tbl_Users US
inner join
	dbo.tbl_UserLogins AS ULO ON ULO.userLogin_userID = US.userID
WHERE 
	ULO.userLogin_magJournalCode = 'GP'
AND
	(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND
	US.userID NOT IN 
	(
		SELECT DISTINCT
			U.userID
		FROM 
			dbo.tbl_Users U
		inner join
			dbo.tbl_UserLogins AS UL ON UL.userLogin_userID = U.userID
		WHERE
			UL.userLoginDate >= dateadd(month, -12, getdate())
	)
GROUP BY
	US.userID,
	userTitle,
	userFirstName,
	userLastName,
	userEmail
ORDER BY
	userLastName,
	userFirstName

====
Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-18 : 05:08:23

[code]
SELECT
US.userID,
US.userTitle,
US.userFirstName,
US.userLastName,
US.userEmail,
ULO.userLastLoginDate
FROM
dbo.tbl_Users US
LEFT JOIN
(
SELECT userLogin_userID, MAX(userLoginDate) AS userLastLoginDate
FROM dbo.tbl_UserLogins
WHERE userLogin_magJournalCode = 'GP'
GROUP BY userLogin_userID
) AS ULO ON ULO.userLogin_userID = US.userID
WHERE
(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND
(
ULO.userLogin_userID IS NULL
OR ULO.userLastLoginDate < dateadd(month, -12, getdate())
)
ORDER BY
US.userLastName,
US.userFirstName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-18 : 05:45:21

[code];WITH cteSource(UserID, LoginDate)
AS (
SELECT UserID,
MAX(LoginDate) AS LoginDate
FROM (
SELECT UserID,
LoginDate
FROM dbo.Logins

UNION ALL

SELECT UserID,
NULL AS LoginDate
FROM dbo.Users
) AS d
GROUP BY UserID
HAVING MAX(LoginDate) < DATEADD(YEAR, -1, GETDATE())
OR MAX(LoginDate) IS NULL
)
SELECT c.UserID,
u.Name,
c.LoginDate
FROM cteSource AS c
INNER JOIN dbo.Users AS u ON u.UserID = c.UserID[/code]

N 56°04'39.26"
E 12°55'05.63"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 07:08:22

Thanks for the replies guys - very interesting, i'll take a look at both of those sometime today.

I didn't need the LEFT JOIN in the end as they're not interested in anyone who has never logged in. i.e. never logged in = no entry in login table. so don't need the logindate = NULL part i think

====
Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-06-18 : 07:16:43

[code];WITH cteSource(UserID, LoginDate)
AS (
SELECT UserID,
MAX(LoginDate) AS LoginDate
FROM dbo.Logins
GROUP BY UserID
HAVING MAX(LoginDate) < DATEADD(YEAR, -1, GETDATE())
OR MAX(LoginDate) IS NULL
)
SELECT c.UserID,
u.Name,
c.LoginDate
FROM cteSource AS c
INNER JOIN dbo.Users AS u ON u.UserID = c.UserID[/code]

N 56°04'39.26"
E 12°55'05.63"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 07:30:18

That's great - thankyou. Helped me spot a problem i had in my query as well as yours returns an extra 400 users or so...and runs faster

quote:

Originally posted by khtan


SELECT
	US.userID,
	US.userTitle,
	US.userFirstName,
	US.userLastName,
	US.userEmail,
	ULO.userLastLoginDate
FROM
	dbo.tbl_Users US
LEFT JOIN
	(
		SELECT	userLogin_userID, MAX(userLoginDate) AS userLastLoginDate
		FROM	dbo.tbl_UserLogins 
		WHERE	userLogin_magJournalCode = 'GP'
		GROUP BY userLogin_userID
	) AS ULO 	ON ULO.userLogin_userID = US.userID
WHERE 	
	(US.useremail NOT LIKE '%haynet%' AND US.useremail NOT LIKE '%haymarket%')
AND	
	(
		ULO.userLogin_userID	IS NULL
	OR	ULO.userLastLoginDate	< dateadd(month, -12, getdate())
	)
ORDER BY
	US.userLastName,
	US.userFirstName

KH
[spoiler]Time is always against us[/spoiler]

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 07:47:26

this is very interesting.

where would i specify my WHERE clause(s) in it?

i.e. these two from their respective tables...
tbl_UserLogins: userLogin_magJournalCode = 'GP'
tbl_users: useremail NOT LIKE '%haynet%' AND useremail NOT LIKE '%haymarket%'

modified for my field names...


;WITH cteSource(UserID, LoginDate)
AS (
	SELECT		userLogin_userID,
			MAX(userLoginDate) AS userLoginDate
	FROM		dbo.tbl_UserLogins
	GROUP BY	userLogin_userID
	HAVING		MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
--			OR MAX(userLoginDate) IS NULL
)
SELECT		c.UserID,
		u.userTitle,
		u.userFirstName,
		u.userLastName,
		u.userEmail,
		c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.tbl_Users AS u ON u.UserID = c.UserID

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 09:05:47

like so i think. Hmmm strangely the count from this query is 2 higher than the other one.


;WITH cteSource(UserID, LoginDate, JournalCode)
AS (
	SELECT		userLogin_userID,
				MAX(userLoginDate) AS userLoginDate,
				userLogin_magJournalCode
	FROM		dbo.tbl_UserLogins
	GROUP BY	userLogin_userID,
	userLogin_magJournalCode
	
	HAVING		MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
	and
		userLogin_magJournalCode = 'GP'
--			OR MAX(userLoginDate) IS NULL
)
SELECT		c.UserID,
			u.userTitle,
			u.userFirstName,
			u.userLastName,
			u.userEmail,
			c.LoginDate
FROM		cteSource AS c
INNER JOIN	dbo.tbl_Users AS u ON u.UserID = c.UserID
where
useremail NOT LIKE '%haynet%' AND useremail NOT LIKE '%haymarket%'
order by
	userLastname,
	userFirstName

quote:
Originally posted by KnooKie

this is very interesting.

where would i specify my WHERE clause(s) in it?

i.e. these two from their respective tables...
tbl_UserLogins: userLogin_magJournalCode = 'GP'
tbl_users: useremail NOT LIKE '%haynet%' AND useremail NOT LIKE '%haymarket%'

modified for my field names...

;WITH cteSource(UserID, LoginDate)
AS (
SELECT userLogin_userID,
MAX(userLoginDate) AS userLoginDate
FROM dbo.tbl_UserLogins
GROUP BY userLogin_userID
HAVING MAX(userLoginDate) < DATEADD(YEAR, -1, GETDATE())
-- OR MAX(userLoginDate) IS NULL
)
SELECT c.UserID,
u.userTitle,
u.userFirstName,
u.userLastName,
u.userEmail,
c.LoginDate
FROM cteSource AS c
INNER JOIN dbo.tbl_Users AS u ON u.UserID = c.UserID

====
Paul

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 09:10:20

i wonder if the slightly higher count is to do with the difference between these...

DATEADD(YEAR, -1, GETDATE()

dateadd(month, -12, getdate()

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 09:12:09

Nope

quote:
Originally posted by KnooKie

i wonder if the slightly higher count is to do with the difference between these...

DATEADD(YEAR, -1, GETDATE()

dateadd(month, -12, getdate()

====
Paul

====
Paul

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2012-06-18 : 09:47:56

Great - both giving same results - thanks again

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources