## 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.

• What does your actual select query look like now? - chucksteel 4 years ago
• I dont have a select built for this one becuase i just dont know how to do anything with the due date column, or how i'd make it count that day Plus the others. - brianfulcher15 4 years ago
• I was thinking it woudl be something like

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 4 years ago
• But i have no clue if thats even how the date calculation would work. I cant find any documentation for calculating dates in kace. - brianfulcher15 4 years ago
• select HD_TICKET.*
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 4 years ago
• tried the above too but had no luck - brianfulcher15 4 years ago

Posted by: JasonEgg 4 years ago
0
`SELECT * FROM HD_TICKETWHERE  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)`

#### Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

question