How to find if user not logged in for 45 days

Question

1.00/5 (2 votes)

See more:

Hello,

I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)

So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and i am inserting into login table.

login table

usercode     email          login_date
001          a@gmail.com    2015-11-18 22:02:41.153
001          a@gmail.com    xxx

I have another table called userLoged where i have column usercode,email and web_access

UserLoged table

usercode email         web_access
001         a@gmail.com   Y

Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N'

how to know if he /she not logged for 45 days.

Posted 20-Nov-15 9:47am

christhuxavier

Updated 20-Nov-15 10:31am

Maciej Los

v3

Add a Solution

Comments

ZurdoDev 20-Nov-15 15:58pm

You are storing the login_date so it should be very easy to know if that login_date was more than 45 days ago. Where exactly are you stuck?

Sergey Alexandrovich Kryukov 20-Nov-15 16:03pm

And what's the problem? Don't you support the latest logon time for each user? If you do, the solution is a matter of a simple query.
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-11-20T10:43:00

Solution 1

All you need is to use DATEDIFF[^] function.

SQL

SELECT CASE usercode, login_date, CASE WHEN DATEDIFF(dd, login_date, GETDATE())>45 THEN 'N' ELSE 'Y' END AS Result
FROM (
    SELECT usercode, MAX(login_date) AS login_date 
    FROM [login]
    GROUP BY usercode
) AS T

Posted 20-Nov-15 10:43am

Maciej Los

Updated 20-Nov-15 10:46am

v3

Patrice T · Answer 2 · 2015-11-20T11:04:00

Solution 2

It smells like a design problem.
Why you don't store the user LastLoginDdate in UserLoged table, it would simplify things.

Duplicating email in login table can be considered as a bad idea.

Posted 20-Nov-15 11:04am

Patrice T