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!

Oracle help required to build relationship between months

Discussion in 'SQL PL/SQL' started by gauravgoyaldlp, Jan 21, 2015.

  1. gauravgoyaldlp

    gauravgoyaldlp Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I have a month table where we are storing months from 1994 to 2030 in the form of 1994M01, 1994M02, 1994M03....1994M12, 1995M01 and so on. By using this I need to populate one relation table as below.

    month_id Rolling_month rolling_prev_month rank
    2015M01 2015M01 2014M01 1
    2015M01 2014M12 2013M12 2
    2015M01 2014M11 2013M11 3
    2015M01 2014M10 2013M10 4
    2015M01 2014M09 2013M09 5
    2015M01 2014M08 2013M08 6
    2015M01 2014M07 2013M07 7
    2015M01 2014M06 2013M06 8
    2015M01 2014M05 2013M05 9
    2015M01 2014M04 2013M04 10
    2015M01 2014M03 2013M03 11
    2015M01 2014M02 2013M02 12
    2014M12 2014M12 2013M12 1
    2014M12 2014M11 2013M11 2
    2014M12 2014M10 2013M10 3
    2014M12 2014M09 2013M09 4
    2014M12 2014M08 2013M08 5
    2014M12 2014M07 2013M07 6
    2014M12 2014M06 2013M06 7
    2014M12 2014M05 2013M05 8
    2014M12 2014M04 2013M04 9
    2014M12 2014M03 2013M03 10
    2014M12 2014M02 2013M02 11
    2014M12 2014M01 2013M01 12

    Can anyone help me to achive this by using oracle query or procedure??

    Thanks in advance!

    Regards,
    Gaurav
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Gaurav,

    One way of doing it is as follows:

    Code (SQL):
    WITH periods AS (
      SELECT '2015M01' month_id FROM dual UNION ALL
      SELECT '2014M12' FROM dual
    )
    SELECT DISTINCT month_id,
      regexp_replace(TO_CHAR(ADD_MONTHS(TO_DATE(regexp_replace(month_id, 'M', ''), 'YYYYMM'), -level+1), 'YYYYMM'),'([0-9]{4})', '\1M') rolling_month,
      regexp_replace(TO_CHAR(ADD_MONTHS(TO_DATE(regexp_replace(month_id, 'M', ''), 'YYYYMM'), -level+1-12), 'YYYYMM'), '([0-9]{4})', '\1M') rolling_prev_month,
      level rank
    FROM periods
      CONNECT BY LEVEL <=12
    ORDER BY month_id DESC,
      rolling_month DESC ;
    P.S: I've used the WITH statement as a temporary table with the 2 rows for demo. You don't need this part - just use your table name instead of table "periods".
     
  3. gauravgoyaldlp

    gauravgoyaldlp Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks a lot rajen for your quick help. This is what I was looking for but I am not able to understand the query as I have not used regular expression so far. Could you please explain how this query works?

    Thanks,
    Gaurav
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Gaurav,

    You can get lots of documentation on the net, for ex., Oracle Docs :
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm


    To summarize:

    => Replaces any occurrence of the character "M" in a text with '' (empty string), for ex. '2015M01' => '201501' which is converted to date in the query using 'YYYYMM' format mask.

    => Used to insert the character "M" after the 4th ({4}) numeric character ([0-9]). "\1" used to copy the 1st 4 numbers in the final converted string.