Quote:
Originally Posted by singularity
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;
dBforums
>
Creating a row for each month
01-24-13, 13:49
Creating a row for each month
Linear Mode
