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!

Selecting last non-zero number from a cell in a column

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

  1. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    We have some records coming from another source counting the amount of members. However, sometimes the members does not get tracked and then goes to zero. The problem is is we have another column calculating the amount of new members. When the total members goes to zero then new members is a huge negative number.

    How would I write a statement that grabs the last non- zero number in a column and inserts it where there is a zero.

    For example we could have
    ttl_memeber, new_members as
    10,1
    9,1
    8,1
    0,0
    0,-5
    5,1
    4,1
    0,0
    0,-1
    1,0

    What I want (since we are guessing the data will still not be 100% accurate
    ttl_memeber, new_members as
    10,1
    9,1
    8,2
    6,0
    6,1
    5,1
    4,3
    1,0
    1,0
    1,0

    This way we would at least get the right amount (9) of new members by summing the new_member column as opposed to the amount from before (-1)

    My first attempt was to do a case statement and make a new column with the adjusted values.

    CASE when ttl_members = 0 THEN
    lag(ttl_members,1,0) OVER(ORDER BY date)
    ELSE
    ttl_members
    END

    This however only works for one missing number, and I am not sure how to select from the new values entered. Ideas?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Your question is vague.

    1. what is the logic in calculating ttl_memeber and new_members when they are zero ?
    2. And how the existing values are changed even if they are not zero ?
     
  3. jedwards

    jedwards Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    1- There are no rules set in place to count total members (ttl_members), because this data comes from a file we are sent from a third party.
    2- I am not sure what you are asking.,

    Basically if there is a zero in the column ttl_members, I no longer want to use our calculation to get new members. New members is calculated by subtracting the previous row’s ttl_members value from that of the current row. Does that make sense?