| Author |
 Topic  |
|
|
KnooKie
Aged Yak Warrior
United Kingdom
623 Posts |
 Posted - 02/12/2013 : 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
India
47173 Posts |
Posted - 02/12/2013 : 11:41:45
|
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/
|
 |
|
|
KnooKie
Aged Yak Warrior
United Kingdom
623 Posts |
Posted - 02/12/2013 : 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
India
47173 Posts |
Posted - 02/12/2013 : 12:12:57
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Exclude Users Who logged in Within Last 3 months
