Looking at the question, there were a couple of points raised:
- The escalation needs to catch any work order that may be past due on a 30d frequency.
- The escalation needs to repeat every 30d, so the Escalation should send another notice on a 30d frequency.
- The escalation needs to run daily so the recipients don't get overwhelmed with notifications if the Escalation was run every 30d.
Forum user @winterZZR1400 came up with the start of an elegant solution:
Try in the Condition
MOD(((statusdate - sysdate)- MOD(statusdate - sysdate,1))/90,1)= 0
I took what he posted and used a literal translation for testing, but that didn't work for me. The problem I ran into was the escalation ran once a day and the modulus on
statusdate may not accurately catch a work order based modulus value with
getdate() and the run time of the Escalation.
I looked at the code and determined the date/time stamp of
getdate() needed to be converted over to a date format. I converted both
getdate() to a
yyyy-mm-dd ISO8601 format to see if the difference between the dates as an integer in days and check that the result was a modulus of 30.
The new Escalation Point:
(DATEDIFF(day, CONVERT(date, statusdate, 23),CONVERT(date, getdate(), 23)) % 30) = 0
The overall Escalation looks like this:
Now when the a work order is over due, based on a 30d frequency, the maintenance team will get the following reminder:
The following work order has not had a status update in more than 180d. Please review the WO and check to see if the record needs to be forced to CLOSED status. If not, review the WO for a more appropriate status until further action can be taken.
WO Num: 888888
Descr: INSPECT COTTON CANDY MACHINE FOR BUILD UP
Last Status Date: 4/9/19 3:24 PM
* * Automated Message #1234 - Please Do Not Respond * *
What... you don't have a cotton candy machine where you work?