#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    3
    Rep Power
    0

    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

    Cheers
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    You could try something llike this:
    Code:
    UPDATE calls
       SET severity_level = GREATEST( 4 - TRUNC(( SYSDATE - date_time )*24 ), 1)
    WHERE SYSDATE > date_time;

IMN logo majestic logo threadwatch logo seochat tools logo