SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Exclude Users Who logged in Within Last 3 months
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KnooKie
Aged Yak Warrior

United Kingdom
623 Posts

Posted - 02/12/2013 :  11:30:43  Show Profile  Visit KnooKie's Homepage  Reply with Quote
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

India
52325 Posts

Posted - 02/12/2013 :  11:41:45  Show Profile  Reply with Quote

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


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

Go to Top of Page

KnooKie
Aged Yak Warrior

United Kingdom
623 Posts

Posted - 02/12/2013 :  11:49:32  Show Profile  Visit KnooKie's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/12/2013 :  12:12:57  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000