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.
Author |
Topic |
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2013-02-12 : 11:30:43
|
Hi AllI'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.userlogindateFROM u.f_sub_site ssINNER JOIN dbo.tbl_Users u ON u.userID = ss.useridLEFT JOIN dbo.tbl_userlogins ul ON ul.userlogin_userid = u.useridWHERE 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.userlogindateFROM u.f_sub_site ssINNER JOIN dbo.tbl_Users u ON u.userID = ss.useridLEFT JOIN dbo.tbl_userlogins ul ON ul.userlogin_userid = u.useridWHERE ss.magjournalcode IN ( 'THI' ) AND ul.userlogindate >= DATEADD(MONTH,-3,GETDATE())GROUP BY u.userid ,u.userEmail ,u.usertitle ,u.userfirstname ,u.userlastnameHAVING MAX(userlogindate) < DATEADD(MONTH,-3,GETDATE()))ORDER BY u.userlastname ,u.userfirstname ,u.useremail[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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.userlogindateFROM u.f_sub_site ssINNER JOIN dbo.tbl_Users u ON u.userID = ss.useridLEFT JOIN dbo.tbl_userlogins ul ON ul.userlogin_userid = u.useridWHERE ss.magjournalcode IN ( 'THI' ) --AND -- ul.userlogindate >= DATEADD(MONTH,-3,GETDATE())GROUP BY u.userid ,u.userEmail ,u.usertitle ,u.userfirstname ,u.userlastnameHAVING 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|