Select query

Question

Hi, we are using sql server 2016

declare @tmpUser table
(
userid int,
username varchar(100),
startdate datetime,
enddate datetime,
StatusID int
)

declare @tmpUserLogHistory table
(
LogId int,
userid int,
reason varchar(50),
ChangeDateTime datetime
)

insert into @tmpUser
select 1, 'User1', '05-01-2017', NULL, 1
Union
select 2, 'User2', '05-05-2018', NULL, 1
Union
select 3, 'User3', '07-01-2019', '07-31-2020', 2

select * from @tmpUser

Insert into @tmpUserLogHistory
SELECT 1, 1, 'Login', '05-02-2017 12:00:00:00'
Union
Select 2, 1, 'Add instruction', '05-02-2017 12:05:00:00'
Union
select 3, 1, 'Updated stocks data', '05-02-2017 13:05:00:00'
union
SELECT 4, 1, 'Login', '05-02-2017 16:30:00:00'
Union
Select 5, 1, 'sent email to the user', '05-02-2017 16:35:00:00'
Union
SELECT 6, 2, 'Login', '05-05-2018 15:45:00:00'
Union
Select 7, 2, 'Add Check', '05-05-2018 16:00:00:00'
Union
SELECT 8, 3, 'Login', '07-05-2020 10:45:00:00'
Union
Select 9, 3, 'Contact User', '07-15-2018 16:00:00:00'

select * from @tmpUserLogHistory

I should get all users who are active and who didn't login in the past 120 days.
In above case User1 and User 2 who are active but not logged in past 120 days.

Thanks for your help

Answer 1

I couldn't understand the requirement completely but below query should help you

SELECT DISTINCT u.userid
    ,u.username
FROM @tmpUser AS u
JOIN @tmpUserLogHistory AS h ON u.userid = h.userid
WHERE u.StatusID = 1
    AND h.reason = 'Login'

AND h.ChangeDateTime < DATEADD(DAY, - 120, GETDATE())

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

select   u.userid 
from @tmpUserLogHistory h join @tmpUser u on h.userid=u.userid
where reason='login' and u.StatusID=1
group by u.userid
having max(ChangeDateTime)<dateadd(day,-120,getdate())

Answer 3

You can use CTE to list the most recent login date and time of each user and then use JOIN to list the users who haven't logged in for 120 days.

;WITH CTE_List_Most_Recent_Login_Users AS (
    SELECT userid, MAX(ChangeDateTime) AS ChangeDateTime
    FROM @tmpUserLogHistory
    GROUP BY userid
)

SELECT u.*, h.ChangeDateTime
FROM @tmpUser AS u
INNER JOIN CTE_List_Most_Recent_Login_Users AS h ON u.userid = h.userid
WHERE (u.enddate IS NULL OR u.enddate > GETDATE()) 
  AND DATEDIFF(DAY, h.ChangeDateTime, GETDATE()) > 120;

Answer 4

Hi Vaibhav,
Thanks for quick response. I need to get list of all active users who didn't log in past 120 days. If I get list, then I should send an email to them indicating that they are inactive for pas 120 days and they will be disabled soon.

I added 2 more login row after 2 years for user 1. Your query still returns user1 and user2. It should return only User2 because he hasn't logged in 2 years. User1 logged in recently.

declare @tmpUser table
(
userid int,
username varchar(100),
startdate datetime,
enddate datetime,
StatusID int
)

declare @tmpUserLogHistory table
(
LogId int,
userid int,
reason varchar(50),
ChangeDateTime datetime
)

insert into @tmpUser
select 1, 'User1', '05-01-2017', NULL, 1
Union
select 2, 'User2', '05-05-2018', NULL, 1
Union
select 3, 'User3', '07-01-2019', '07-31-2020', 2

select * from @tmpUser

Insert into @tmpUserLogHistory
SELECT 1, 1, 'Login', '05-02-2017 12:00:00:00'
Union
Select 2, 1, 'Add instruction', '05-02-2017 12:05:00:00'
Union
select 3, 1, 'Updated stocks data', '05-02-2017 13:05:00:00'
union
SELECT 4, 1, 'Login', '05-02-2017 16:30:00:00'
Union
Select 5, 1, 'sent email to the user', '05-02-2017 16:35:00:00'
Union
SELECT 6, 2, 'Login', '05-05-2018 15:45:00:00'
Union
Select 7, 2, 'Add Check', '05-05-2018 16:00:00:00'
Union
SELECT 8, 3, 'Login', '07-05-2020 10:45:00:00'
Union
Select 9, 3, 'Contact User', '07-15-2018 16:00:00:00'
union
SELECT 10, 1, 'Login', '08-02-2020 12:00:00:00'
Union
Select 11, 1, 'Add instruction', '08-02-2020 12:05:00:00'

select * from @tmpUserLogHistory

I should get all users who are active and who didn't login in the past 120 days.
In above case User1 logged in after 2 years. So, it shouldn't pick up USER1 and Only User 2 who is active but not logged in past 120 days.

Thanks for your help

Answer 5

I wasn't clear about how you wanted to decide if a user was active. If you meant a user was active because status = 1, then

Select u.userid
From @tmpUser u
Where u.StatusID = 1
  And Not Exists(Select * From @tmpUserLogHistory h Where u.userid = h.userid 
    And DateDiff(day, h.ChangeDateTime, GetDate()) <= 120) 

If you meant a user was active because today's date is between startdate and enddate, then

Select u.userid
From @tmpUser u
Where GETDATE() Between u.startdate And IsNull(u.enddate, GetDate())
  And Not Exists(Select * From @tmpUserLogHistory h Where u.userid = h.userid 
    And DateDiff(day, h.ChangeDateTime, GetDate()) <= 120) 

Tom