Change priority if due date within x days
I need some help figuring out a select.
I need a rule that will look at the due date and if its within 20 days make the ticket a medium as long as the ticket is not already high or critical, if its within 5 days make it a high unless its a critical priority.
So basically
if due date <20days and priority is not high or crit
then priority = medium
if due date <5 days and priority is not crit
then priority = high.
I need a rule that will look at the due date and if its within 20 days make the ticket a medium as long as the ticket is not already high or critical, if its within 5 days make it a high unless its a critical priority.
So basically
if due date <20days and priority is not high or crit
then priority = medium
if due date <5 days and priority is not crit
then priority = high.
6 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
JasonEgg
8 years ago
How about this:
SELECT * FROM HD_TICKET
WHERE
HD_STATUS_ID NOT IN (***closed state ID #s***)
AND DUE_DATE < ADDDATE(NOW(),INTERVAL 5 DAY)
AND HD_PRIORITY_ID NOT IN (***priority ID #s***)
On the line for DUE_DATE, you can change the number before "DAY" from 5 to 20. For the starred sections you'll need to supply the appropriate IDs. For example, if High priority has ID #5 and Critical has ID #6 then the last line would look like this:
AND HD_PRIORITY_ID NOT IN (5,6)
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (((date(HHD_TICKET.DUE_DATE) < curdate-5() )) and HD_TICKET.HD_QUEUE_ID = 1 ) - brianfulcher15 8 years ago
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
where date >= HD_TICKET.DUE_DATE(CURDATE(), INTERVAL 5 DAY) AND date <= CURDATE() - brianfulcher15 8 years ago