SQL query for getting last login

Question

Hi,
We have ASP.net application and sql server 2016 database. When ever user login to application, new row will be added for that user to log table.
Row will have userID, type (login or logout or session end etc), LoginDateTime.
Each day can have multiple rows for login and logout.

Management wants to automatically send

• an email to the user if he\she not logged in last 100 days indicating their account will be disabled after 7days.
• Another email on 106th day indicating account will be disabled next day.

declare @Loughborough table (
userID int,
type varchar(100),
ChangeDateTime datetime
)

Insert into @Loughborough
select 1, 'Log In', '2020-06-01 9:30:00'
union
select 1, 'Log Out', '2020-06-01 9:45:00'
union
select 1, 'Log In', '2020-06-01 16:30:00'
union
select 1, 'Log Out', '2020-06-01 17:45:00'
union
select 2, 'Log In', '2020-12-01 9:40:00'
union
select 2, 'Log Out', '2020-12-01 11:40:00'
union
select 3, 'Log In', '2021-01-11 11:00:00'
union
select 3, 'Log Out', '2021-01-11 11:45:00'

select * from @Loughborough

-- User last log in date is greater than 100 days
-- The sql query should return userID 1

Answer 1

Try this:

select userID, MAX(ChangeDateTime) AS ChangeDateTime
from @log
where [type] = 'Log In'
GROUP BY userID
HAVING MAX(ChangeDateTime) < DATEADD(DAY,-100,GETDATE())

Answer 2

Hi @Spunny ,

Thank you so much for posting here in Microsoft Q&A.

You could also use DATEDIFF function as below:

 SELECT userID  
 FROM @log  
 WHERE [type] = 'Log In'  
 GROUP BY userID  
 HAVING DATEDIFF(DAY,MAX(ChangeDateTime),GETDATE())>100  

Output:
1

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.