1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to subtract a row from previous row and sum the result

Discussion in 'General' started by hrudayareddy, Feb 15, 2011.

  1. hrudayareddy

    hrudayareddy Guest

    Hi i have the following result when I run a particular query


    TIME | ID | NEW_VALUE | OLD_VALUE

    1/4/2011 12:07 | 253802 | New |(Null)
    1/4/2011 13:04 | 253802 | Investigating |New
    1/5/2011 13:04 | 253802 | New |Investigating
    1/5/2011 15:04 | 253802 | Investigating |New
    1/5/2011 20:04 | 253802 | Fixed |Investigating
    1/5/2011 20:15 | 253802 | Delivered |Fixed
    1/5/2011 21:06 | 253802 | Retest |Delivered
    1/6/2011 22:07 | 253802 | New |Retest
    1/6/2011 23:04 | 253802 | Investigating |New

    I want to calculate the following:

    Everytime OLD_VALUE= New, I need to subtract the TIME of the current row with the TIME of the previous row that will be nothing but NEW_VALUE = New and add the differences.

    Can anyone tell me how to do it in Oracle.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Use CASE and LAG:

    case when old_value is null then (time - lag(time) over (order by time))*24 else null end prod_hrs