get last date and compare it

Question

0.00/5 (No votes)

See more:

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.

Posted 29-Mar-11 14:19pm

shms_rony

Updated 31-Mar-11 4:46am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 31-Mar-11 10:47am

Edited for Grammar, Syntax and Readability.

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)

Venkatesh Mookkan · Accepted Answer · 2011-03-29T16:38:00

Solution 1

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

SQL

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

Now write your logic to update table2 as you like.

Posted 29-Mar-11 16:38pm

Venkatesh Mookkan

v2

Comments

Dalek Dave 31-Mar-11 10:47am

Good Call.

Ratnesh N Bharos · Accepted Answer · 2011-03-29T23:59:00

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.