获取最后的日期并进行比较 [英] get last date and compare it

问题描述

大家好，

我有两个桌子

table1:
_____________________
ID |结束日期|用户ID
_____________________
1 | 27/10/2010 | 1
2 | 28/3/2011 | 2
3 | 30/3/2011 | 1
4 | 30/4/2011 | 2
_____________________

table2:
_______________________
用户名|雕像|值
_______________________
1 |活动| 0
2 |活动| 0
_______________________


我想从table1中获取用户的上次插入日期，如果datenow> = lastdate
更新table2并将雕像从活动状态更改为暂停状态.

如果datenow< lastdate然后更新teble2并将值更改为1，第二天更改为2直到datenow = lastdate并更改雕像.

Hi all,

I have two tables

table1:
_____________________
id |enddate|userid
_____________________
1 |27/10/2010|1
2 |28/3/2011 |2
3 |30/3/2011 |1
4 |30/4/2011 |2
_____________________

table2 :
_______________________
userid | statues |value
_______________________
1 |active |0
2 |active |0
_______________________


I want to get last inserted date for user from table1 and if datenow>= lastdate
update table2 and change statues from active to suspended .

If datenow < lastdate then update teble2 and change value to 1 and next day change to 2 until datenow=lastdate and change statues.

推荐答案

您可以使用以下查询:

You can get userid and last inserted enddate using the following query:

SELECT DISTINCT userid, MAX(enddate) AS enddate FROM table1
GROUP BY userid

现在编写您的逻辑来更新table2.

Now write your logic to update table2 as you like.

我编写了一个满足您要求的SP，它以userid作为输入参数.在SP中，它将选择该用户的最新结束日期，并与当前日期进行比较.根据条件还可以更新状态.

I write one SP that meets your requirement it takes userid as input parameter. In the SP it picks latest enddate of that user and compare with current date. According to the condition it update status also.

create procedure spCheckStatus @userid int
as
begin
declare @EndDate date
declare @CurrentDate date

select @EndDate=max(enddate) from table1 where userid=@userid
select @CurrentDate=getdate()

if @CurrentDate >= @EndDate
begin
update table2 set status=''suspend'' where userid=@userid
end

if @CurrentDate < @EndDate
begin
update table2 set status=''active'' where userid=@userid
end

end

试试....希望它能为您服务.

try it....Hope its work for you.

这篇关于获取最后的日期并进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！