December 2nd, 2011, 08:46 AM
Sysdate if statement procedure help
Hi guys, I'm new here. I'm working on a project and I can't figure out the procedure I will need to use. I've got a sysdate field in my "Calls" table which generates a call date + time, however I need to insert a severity level of the call after a certain amount of time, eg after 1 hour level turns from level 4 to level 3 and so forth until reaching level 1 after x amount of time. I know I'd need to put in an else if statement within this procedure I just can't work out how to do it with the sysdate field that has also been created. Any help would be greatly appreciated.
A bit of info about the table, table name = calls, column 1 = date_time and column 2 is called severity_level
December 2nd, 2011, 11:00 AM
You could try something llike this:
SET severity_level = GREATEST( 4 - TRUNC(( SYSDATE - date_time )*24 ), 1)
WHERE SYSDATE > date_time;