Needed a dummy month to be displayed - Oracle SQL

Question

For monthly fees reporting purpose, needed to display the output as mentioned below . The data will be entered in the table only if the fees is paid for that month otherwise the month will not be present in the table.

on executing query

select reg_id, dop,to_char(dop, 'FMMonth,YYYY') Month, status 
  from fees_status
 where dop between ('1-Jul-2012') and ('1-Dec-2012')

the output is

REG_ID  DOP         MONTH           STATUS

AE-8    7/1/2012    July,2012       PAID
AE-8    9/1/2012    September,2012  PAID
AE-8    11/1/2012   November,2012   PAID
AE-8    12/1/2012   December,2012   PAID

For Reporting Purpose, Needed the output as

REG_ID  DOP         MONTH           STATUS

AE-8    7/1/2012    July,2012       PAID
AE-8    8/1/2012    August,2012     NOTPAID
AE-8    9/1/2012    September,2012  PAID
AE-8    10/1/2012   October,2012    NOTPAID
AE-8    11/1/2012   November,2012   PAID
AE-8    12/1/2012   December,2012   PAID

Can someone help in displaying the output as mentioned in the above?

Answer 1

You can use partition by extension of outer join to achieve that. Here is an example:

-- sample of data from your question 
SQL> with fees_status(REG_ID, DOP, MONTH1, STATUS) as(
  2    select 'AE-8', to_date('7/1/2012', 'MM/DD/YYYY'), 'July,2012'     ,'PAID' from dual union all
  3    select 'AE-8', to_date('9/1/2012', 'MM/DD/YYYY'), 'September,2012','PAID' from dual union all
  4    select 'AE-8', to_date('11/1/2012', 'MM/DD/YYYY'),'November,2012' ,'PAID' from dual union all
  5    select 'AE-8', to_date('12/1/2012', 'MM/DD/YYYY'),'December,2012' ,'PAID' from dual
  6  ), -- dates
  7  dates(dt) as(
  8    select add_months(to_date('01/01/2012', 'mm/dd/yyyy'), level - 1)
  9      from dual
 10    connect by level <= 12
 11  )
 12  select t.reg_id
 13       , d.dt as dop
 14       , to_char(d.dt, 'fmMonth, YYYY') as month1
 15       , Nvl(t.status, 'NOTPAID')       as status
 16   from fees_status t
 17   partition by (t.reg_id)
 18   right outer join dates d
 19      on (d.dt = t.dop)
 20   where d.dt between (to_date('1-Jul-2012', 'dd-Month-yyyy'))
 21                  and (to_date('1-Dec-2012', 'dd-Month-yyyy'))
 22  ;

REG_ID DOP         MONTH1                STATUS
------ ----------- -------------------- -------
AE-8   01-Jul-12   July, 2012            PAID
AE-8   01-Aug-12   August, 2012          NOTPAID
AE-8   01-Sep-12   September, 2012       PAID
AE-8   01-Oct-12   October, 2012         NOTPAID
AE-8   01-Nov-12   November, 2012        PAID
AE-8   01-Dec-12   December, 2012        PAID

6 rows selected