SQL Query Question

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  October 6, 2009 at 7:49 am

  #226861

  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?

• Mark Marinovic

  SSCrazy

  Points: 2472

  More actions

  October 6, 2009 at 7:59 am

  #1062257

  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

• Sergey Vavinskiy

  SSCarpal Tunnel

  Points: 4469

  More actions

  October 6, 2009 at 8:11 am

  #1062267

  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'

• Ken McKelvey

  SSCoach

  Points: 18769

  More actions

  October 6, 2009 at 8:46 am

  #1062306

  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

• mbrady5

  SSCrazy

  Points: 2835

  More actions

  October 6, 2009 at 8:54 am

  #1062314

  thank you both...works GREAT