如何查找用户是否未登录45天 [英] How to find if user not logged in for 45 days

问题描述

您好，



我在sql server中创建了一个名为Login的表，其中我有列usercode，email和login_date（login_date是datetime类型）



所以，我使用.net创建了Web应用程序。每当用户登录时，我允许基于userLoged的表，我将插入登录表。



登录表

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          [e-mail]    2015-11-18 22:02:41.153
001          [e-mail]    xxx

我有另一个名为userLoged的表，其中我有列用户代码，电子邮件和web_access



UserLoged 表

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

UserLoged table

usercode email         web_access
001         [e-mail]   Y

现在，如果 [e-mail]（001）未登录45天，我需要将web_access更新为'N'<​​br />


如何知道他/她是否未登录45天。

Now, if [e-mail] (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.

推荐答案

您只需使用 DATEDIFF [ ^ ]功能。

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

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

它闻起来像是一个设计问题。

为什么你不存储用户 LastLoginDdate 在 UserLoged 表中，它会简化一些事情。



在登录表中复制电子邮件可被视为一个坏主意。

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.

这篇关于如何查找用户是否未登录45天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！