Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Question Creating a row for each month

    I have data as follows:

    assessment_date
    2/29/2012
    9/11/2012
    10/17/2012

    I need to create a row for every month based on the latest assessment_date as of that month:

    assessment_date month
    2/29/2012 2/29/2012
    2/29/2012 3/31/2012
    2/29/2012 4/30/2012
    2/29/2012 5/31/2012
    2/29/2012 6/30/2012
    2/29/2012 7/31/2012
    2/29/2012 8/31/2012
    9/11/2012 9/30/2012
    10/17/2012 10/31/2012
    10/17/2012 11/30/2012
    10/17/2012 12/31/2012

    I have a calendar table but can't figure out how to achieve this result.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,658
    Code:
      1  SELECT Last_day(Add_months(To_date('2012-01-01', 'YYYY-MM-DD'), LEVEL))
      2  FROM   dual
      3* CONNECT BY LEVEL < 12
    SQL> /
    
    LAST_DAY(
    ---------
    29-FEB-12
    31-MAR-12
    30-APR-12
    31-MAY-12
    30-JUN-12
    31-JUL-12
    31-AUG-12
    30-SEP-12
    31-OCT-12
    30-NOV-12
    31-DEC-12
    
    11 rows selected.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    Sorry, that's not what I'm looking for. I already have a calendar table. I need to join the calendar table to the assessment table to show the most recent assessment_date for each month, as shown in my original post.

  4. #4
    Join Date
    Mar 2007
    Posts
    615
    Quote Originally Posted by singularity View Post
    Sorry, that's not what I'm looking for. I already have a calendar table. I need to join the calendar table to the assessment table to show the most recent assessment_date for each month, as shown in my original post.
    So, it would be useful, if you posted what you already have in SQL (CREATE TABLE + INSERT statements) to avoid misinterpretation.
    Based on content of your question, the answer would be: so do the (outer) join on month. Most recent assessment date may be obtained by appropriate Oracle analytic function (MAX seems to fit your needs).
    It is described in SQL Language Reference, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/.

    Anyway, as I am bored, here is a query. Note the WITH clause, which should mimic your data. You should post them next time in SQL.
    Code:
    with your_data as ( select to_date( '29-02-2012', 'dd-mm-yyyy' ) assessment_date from dual
              union all select to_date( '11-09-2012', 'dd-mm-yyyy' ) from dual 
              union all select to_date( '17-10-2012', 'dd-mm-yyyy' ) from dual ),
         your_calendar as ( select last_day( to_date( '2012-'||to_char(level), 'yyyy-mm' ) ) month
                       from dual connect by level <= 12 )
    select month, assessment_date,
      max( assessment_date ) over ( order by month ) latest_assessment_date
    from your_calendar left join your_data on month = last_day( assessment_date )
    order by month;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •