问题描述
大家好，
我有两个桌子
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.
推荐答案
SELECT DISTINCT userid, MAX(enddate) AS enddate FROM table1
GROUP BY userid
现在编写您的逻辑来更新table2.
Now write your logic to update table2 as you like.
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.
这篇关于获取最后的日期并进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！