Requires a dummy month to display - Oracle SQL

Question

For the purposes of monthly reporting of the expenses required to display the results, as indicated below. Data will be entered into the table only if the fees are paid for this month, otherwise the month will not be present in the table.

when executing the request

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

output

 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 purposes, it is necessary to conclude

 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 above?

Answer 1

You can use the extension extension section to achieve this. 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 

Answer 2

 WITH MONTH_COUNTER AS ( SELECT LEVEL-1 AS ID FROM DUAL CONNECT BY LEVEL <= 12 ) SELECT TO_CHAR(ADD_MONTHS(TO_DATE('01/01/2012', 'DD/MM/RRRR'), ID),'dd/mm/yyyy') FROM MONTH_COUNTER; 

This will create a dummy table containing all the months of the year. Make a left join on it to get your requirement.

Hope this helps!