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!

Month counter

Discussion in 'SQL PL/SQL' started by ertweety, Oct 17, 2016.

  1. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I have some data that looks like the following;

    Account Date Status
    1 2016-06-01 Y
    1 2016-07-01 Y
    1 2016-09-01 N
    2 2016-07-01 N
    2 2016-08-01 N

    I would like another column to show by account number the first month listed as 1, the second month as 2, etc. Like the following;

    Account Date Status Month
    1 2016-06-01 Y 1
    1 2016-07-01 Y 2
    1 2016-09-01 N 3
    2 2016-07-01 N 1
    2 2016-08-01 N 2
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    6
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    São Paulo - Brasil
    Try use MONTHS_BETWEEN,

    select MONTHS_BETWEEN(to_date('01/09/2016','dd/mm/yyyy'), to_date('01/01/2016','dd/mm/yyyy')) from dual;
     
  3. ertweety

    ertweety Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    That didn't work for what I was looking for but this did. :)

    select t.*,
    row_number() over(partition by account order by account) rn
    from table t
    order by account, date asc;