October 6, 2009 at 7:49 am
Good Morning,
just want to make sure my query makes sense....pretty new to this stuff.
I have a table that I would like to update a column called 'status' to 'Expired' when the column 'Date Quoted' is 30 days past today's date. Today meaning whenever I run the query. The other condition is that the current status is = 'In Progress'.
Here it is...
update new_call
set status = 'Expired'
Where [Date Quoted] = DATEADD(DD, -30, GETDATE())
and status = '[in progress]'
Is this right?
October 6, 2009 at 7:59 am
mbrady5 (10/6/2009)
Good Morning,just want to make sure my query makes sense....pretty new to this stuff.
I have a table that I would like to update a column called 'status' to 'Expired' when the column 'Date Quoted' is 30 days past today's date. Today meaning whenever I run the query. The other condition is that the current status is = 'In Progress'.
Here it is...
update new_call
set status = 'Expired'
Where [Date Quoted] = DATEADD(DD, -30, GETDATE())
and status = '[in progress]'
Is this right?
That is correct, with the exception of [Date Quoted] =. I think what you want to say is:
WHERE [Date Quoted] < DATEADD(DD, -30, GETDATE())
I would run your statement in a SELECT first to make sure you're selecting the right number of rows to update.
MJM
October 6, 2009 at 8:11 am
mbrady5 (10/6/2009)
Good Morning,just want to make sure my query makes sense....pretty new to this stuff.
I have a table that I would like to update a column called 'status' to 'Expired' when the column 'Date Quoted' is 30 days past today's date. Today meaning whenever I run the query. The other condition is that the current status is = 'In Progress'.
Here it is...
update new_call
set status = 'Expired'
Where [Date Quoted] = DATEADD(DD, -30, GETDATE())
and status = '[in progress]'Is this right?
1. Remove brackets from [in progress], you don't need them
2. Use <= instead of =
3. I would also put the brackets on the column name 'status' since SQL it is a T-SQL command attribute.
The final code UPDATE statement should be
UPDATE new_call
SET [status] = 'Expired'
Where [Date Quoted] <= DATEADD(DD, -30, GETDATE())
and status = 'in progress'
October 6, 2009 at 8:46 am
Or use a calculated column:
ALTER TABLE new_call
ADD CalcStatus AS
CASE
WHEN DATEDIFF(d, [Date Quoted], CURRENT_TIMESTAMP) <= 30
THEN CAST('In Progress' AS varchar(11))
ELSE CAST('Expired' AS varchar(11))
END
October 6, 2009 at 8:54 am
thank you both...works GREAT
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy