If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Creating a row for each month

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,598
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.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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;
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On