Link to home
Start Free TrialLog in
Avatar of wally_davis
wally_davisFlag for United States of America

asked on

SQL 2005 If...Else..Then and DateDiff job

I have three db values I'm working with using and SQL 2005.
I have a job that will need to run daily and determine if the values in the "Devices.Status" field need to be updated. It will look at a field called "Devices.TimeStamp" and then perform a DateDiff function and set the value in the "Devices.Status" field and "Devices.DaysOffline" appropriately.
However, I'm not an experienced SQL Job program writer so am coming here for SQL Expertise help.
What I also need to do is perform an If..Then..Else statement. I will have these values written to the database by a VB Module executable that will run daily to clean up our database: Remove, Research, Disabled, Broke, Unable to Wake and Install PVT. What I need to have happen is, the Job will need to check to see if these Values are already written to the Status field. If they are, then exit the routine so that it doesn't update the Status field or the DaysOffline field. So, I need it to have the code below, and this new routine merged together.
Thank you in advance experts,
update [devices]
  set Status = case 
               when datediff(day, Timestamp, getdate()) <= 1 then 'Alive'
               when datediff(day, Timestamp, getdate())  <= 29 then 'InActive'
               when datediff(day, Timestamp, getdate()) <= 89 then 'Disabled'
               else 'ToDelete' end
 , DaysOffline = datediff(day, Timestamp, getdate())
-- delete [devices]
-- where Status = 'ToDelete'

Open in new window

Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wally_davis


Thanks Daniel for the advice on the DateTime datatype and a simple, yet perfect solution.