I want to add one day to a due_date if a certain criteria happens. How do I accomplish this? I usually get 10 to 15 results in this criteria below and they are usually all different dates.
Update wh.WO_HDR
Set wh.Due_Date = (current due date plus 1)
from WO_HDR wh
join WO_HDR_STATUS ws on (wh.DOC_NO = ws.PARENT_DOC_NO)
where ws.WO_STATUS = 'K110'
and ws.COMPLETED_DATE is null
UPDATE wh
SET wh.Due_Date = DATEADD(DAY, 1, wh.Due_Date)
FROM WO_HDR wh
JOIN WO_HDR_STATUS ws ON ( wh.DOC_NO = ws.PARENT_DOC_NO )
WHERE ws.WO_STATUS = 'K110'
AND ws.COMPLETED_DATE IS NULL;That was so easy. Thank you!
What happens when you run this again - it will just "move on" wh.Due_Date one more day (i.e. the criteria will still be satisfied)
Everytime I run the query, the due date increases one day on the work orders that meet the criteria
OK. Is that what you want? What happens if it gets run twice-in-a-row for some reason?
Just wondering if you need a "last run on" date or somesuch that prevents it doing anything (unless 24 hours / "tomorrow" has elapsed)
I'll be setting it up to run once overnight automatically. Even if it was run manually, I only have under 10 lines effected so if I were to run it twice by mistake, I would just change the query to -1 day and re-run.
And if you're not there or you forget, you have a problem.
You can make this much more bullet proof by adding a "LastModifiedOn" column and not updating anything that was updated in the last, say, 24 hours)
How do I incorporate that into this query?
Your WHERE clause would include a DATEADD of minus 24 hours on GETDATE and compared to the modified date column. If the date in the column is less than 24 hours old when compared to the DATEADD, then the row shouldn't be included for the update.
AND LastModifiedOn <= DATEADD(hh,-24,GETDATE())
Make sure that when you add the LastModifiedOn column to the table, that you have it default to GETDATE() so that new rows won't be affected until they've aged enough.
Thanks. I'll try that when I get back in the office.
Need to add [LastModifiedOn] to the SET statement too, so it gets set (to today's date) when the update runs.
SET wh.Due_Date = DATEADD(DAY, 1, wh.Due_Date),
LastModifiedOn = GetDate()
Not sure about the
DATEADD(hh,-24,GETDATE())
bit. Sorry, I'm making something straightforward into something horribly complicated, but BeenThereDoneThat and all the business about getting the Tee-shirt too ...
For a scheduled task it can run late - maybe a few milliseconds, maybe even a second or two. Then if tomorrow's process runs at the normal time then 24 hours (exactly) won't have elapsed yet.
I think I would want it to only run once per "calendar" day
AND LastModifiedOn < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
which will be midnight last night.
I wonder if there is an easier / more efficient way to do that in current SQL versions? Perhaps CAST as just as efficient? Certainly easlier to read!
AND LastModifiedOn < CAST(GetDate() AS DATE)
a system variable that delivered Now Date, without Now Time, would be handy ...
OK ... here I go again!
What happens if the Scheduled Task doesn't run one night? (Powercut lets say ... or some CockUp that leaves the Task marked DISABLED for several days).
Would it be important to add SEVERAL days to wh.Due_Date to "catch up"?
Points taken and I agree. And what if someone manually changes the due date to something further out? Unless care was taken, even such "future dated" items would be pushed out a day. There's no question that someone needs to sit down and define some requirements and Use Cases for this.
Or not. This is an automated "schedule pusher" that adds days to a due date. What they really need to do is NOT employ a "schedule pusher" and deal with the fact that their items are late. It would be far better to figure out how late things are so that they can get better at estimating schedules instead of sweeping it all under the carpet as if nothing was ever late.
Like Granny used to say "Figures can lie and liars figure".
While I appreciate your concern for pushing due dates out manually, our customers allows it while the product is out for certain repairs that the product needs from time to time. If the products doesn't need repaired, the criteria is never met, thus we stick to the original due date.
If the customers take care of the units, they would get them back sooner. It's not our fault the product comes to us cracked and dented and they know that.
