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!

Subtracting rows variably

Discussion in 'SQL PL/SQL' started by jedwards, Feb 3, 2011.

  1. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    I have rows with data showing the total amount of x. I need to insert a new column that shows the new amount of x between that day and the previous day. So I need to somehow dynamically subtract the previous row's TTL_X from the current rows TTL_X and insert that value in the current row.

    Any ideas?


    Example data set:

    DATE, TTL_X, NEW_X,
    01/01/2011, 50, -,
    01/02/2011, 55, 5,
    01/03/2011, 63, 8,

    If you need clarification, please ask.

    Thanks,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Let us presume you have the following data in a table named mytab:

    CT_DATE, TTL_X, NEW_X,
    01/01/2011, 50,
    01/02/2011, 55, 5
    01/03/2011, 65, 10
    01/04/2011, 70, 5
    01/05/2011, 75,
    01/02/2011, 85, 10

    Using LEAD and CASE provides the the results you desire:

    Code (SQL):
     
    SELECT ct_date, ttl_x, CASE WHEN new_x IS NULL THEN
                                  (lead(ttl_x) OVER (ORDER BY ct_date)) - nvl(ttl_x,0) ELSE new_x END new_x
    FROM mytab;
     
    CT_DATE, TTL_X, NEW_X,
    01/01/2011, 50, 5
    01/02/2011, 55, 5
    01/03/2011, 65, 10
    01/04/2011, 70, 5
    01/05/2011, 75, 5
    01/02/2011, 85, 10

    Updating the table is also fairly simple:

    Code (SQL):
     
    MERGE INTO mytab t
    USING
    (SELECT ct_date, ttl_x, CASE WHEN new_x IS NULL THEN
                                  (lead(ttl_x) OVER (ORDER BY ct_date)) - nvl(ttl_x,0) ELSE new_x END new_x
    FROM mytab) m ON (m.ct_date = t.ct_date)
    WHEN matched THEN
    UPDATE
    SET t.new_x = m.new_x
    WHEN NOT matched THEN
    INSERT
    (ct_date, ttl_x, new_x)
    VALUES(m.ct_date, m.ttl_x, m.new_x);
     
     
  3. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    This is good, i just have a quick question for you. How do I shift the new x' rows by one. The new x is next to the previous day. So it's predicting what the new x will be for the next day...with 100% accuracy. Amazing :)

    Thank you again!!
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Rewrite using the LAG function rather than LEAD:

    lag(ttl_x) Over (Order by ct_date) - nvl(ttl_x,0)

    which should keep the first entry NULL and shift the difference one row.
     
  5. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Oh ha, saw that after. Thanks so much. It's cool to learn new tricks! Thank you!!