Exclude Users Who logged in Within Last 3 months

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)

Exclude Users Who logged in Within Last 3 months

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2013-02-12 : 11:30:43

Hi All

I'm trying to get a list of people who have logged in longer than 3 months ago at the earliest and their login count.

The following gives me users who have logged in within the last 3 months and how many times.
But how do i list users who have logged in earlier than 3 months ago and EXCLUDING any users who have logged in within the last 3 months?

SELECT DISTINCT
	u.userid
	,u.userEmail
	,u.usertitle
	,u.userfirstname
	,u.userlastname
	,Count(ul.userlogindate)	AS LoginCount
	--,ul.userlogindate
FROM 
	u.f_sub_site ss
INNER JOIN 
	dbo.tbl_Users u ON u.userID = ss.userid
LEFT JOIN
	dbo.tbl_userlogins ul ON ul.userlogin_userid = u.userid
WHERE 
	ss.magjournalcode IN (
		'THI'
		)
	AND
		ul.userlogindate >= DATEADD(MONTH,-3,GETDATE())
GROUP BY
	u.userid
	,u.userEmail
	,u.usertitle
	,u.userfirstname
	,u.userlastname
)
ORDER BY
	u.userlastname
	,u.userfirstname
	,u.useremail

I hope that's clear and any pointers appreciated

====
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 11:41:45

[code]
SELECT
u.userid
,u.userEmail
,u.usertitle
,u.userfirstname
,u.userlastname
,Count(ul.userlogindate) AS LoginCount
--,ul.userlogindate
FROM
u.f_sub_site ss
INNER JOIN
dbo.tbl_Users u ON u.userID = ss.userid
LEFT JOIN
dbo.tbl_userlogins ul ON ul.userlogin_userid = u.userid
WHERE
ss.magjournalcode IN (
'THI'
)
AND
ul.userlogindate >= DATEADD(MONTH,-3,GETDATE())
GROUP BY
u.userid
,u.userEmail
,u.usertitle
,u.userfirstname
,u.userlastname
HAVING MAX(userlogindate) < DATEADD(MONTH,-3,GETDATE())
)
ORDER BY
u.userlastname
,u.userfirstname
,u.useremail
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2013-02-12 : 11:49:32

Great thanks - exactly what i needed.

Modified it ever so slightly...

SELECT 
	u.userid
	,u.userEmail
	,u.usertitle
	,u.userfirstname
	,u.userlastname
	,Count(ul.userlogindate)	AS LoginCount
	--,ul.userlogindate
FROM 
	u.f_sub_site ss
INNER JOIN 
	dbo.tbl_Users u ON u.userID = ss.userid
LEFT JOIN
	dbo.tbl_userlogins ul ON ul.userlogin_userid = u.userid
WHERE 
	ss.magjournalcode IN (
		'THI'
		)
	--AND
	--	ul.userlogindate >= DATEADD(MONTH,-3,GETDATE())
GROUP BY
	u.userid
	,u.userEmail
	,u.usertitle
	,u.userfirstname
	,u.userlastname
HAVING MAX(ul.userlogindate) < DATEADD(MONTH,-3,GETDATE())
ORDER BY
	u.userlastname
	,u.userfirstname
	,u.useremail

====
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 12:12:57

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources