0

I need help in writing a query to return distinct emails that have not logged into ABC database in the past 5 months. The exact task I have been asked is as following:

Create a table in ABC database with columns email nvarchar(100) and noticecount int. Name the table Not_loggedinUsers. Create a script that finds users that have not logged into ABC within the past 5 months.

If the email address doesn’t exist in Not_loggedinUsers insert the email address into Not_loggedinUsers and set noticecount to 1. If email address exists, increment noticecount by 1. If noticecount = 3, do not increment noticecount.

Note: I also have a table in ABC database called users that has UserID, Username, UserEmail columns, just in case it needs to be joined or for more info. Please let me know if the question is not clear I can provide more details.

  • Which table records logins? I guess it must have a time-related column, too, since your task mentions a period of time. – Andriy M May 5 '16 at 7:09
0

Assuming you missed a column call LastLoginTime in User table or some other Log table, here goes the query.

USE ABC;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'Not_loggedinUsers' AND type ='U') 
CREATE TABLE Not_loggedinUsers 
( 
Email NVARCHAR(100), 
NoticeCount INT
);

SELECT UserEmail Email
INTO #oldUsers
FROM User
GROUP BY UserEmail 
HAVING DATEPART(M, DATEDIFF(MAX(ISNULL(LastLoginTime, GETDATE() ) ), GETDATE() )) >=5;

UPDATE a
SET NoticeCount = ISNULL(NoticeCount,0) +1
FROM Not_loggedinUsers a
INNER JOIN #oldUsers b ON b.Email = a.Email
WHERE a.NoticeCount < 3;

INSERT INTO Not_loggedinUsers (Email, NoticeCount) 
SELECT Email, 1
FROM #oldUsers 
WHERE Email NOT IN (SELECT Email FROM Not_loggedinUsers);

DROP TABLE #oldUsers;

The only change if you have a different log table is that you need to add another join to fetch LastLoginTime.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.