rajesh@ORA11G> variable b1 varchar2(20);
rajesh@ORA11G> variable b2 varchar2(20);
rajesh@ORA11G> exec :b1 := '01-Jan-2013';
PL/SQL procedure successfully completed.
rajesh@ORA11G> exec :b2 := '31-dec-2013';
PL/SQL procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G> select trunc(created,'mm') dt,count(*) cnt
2 from all_users
3 where created between to_date(:b1,'dd-mon-yyyy')
4 and to_date(:b2,'dd-mon-yyyy')
5 group by trunc(created,'mm')
6 order by 1 ;
DT CNT
----------- ----------
01-OCT-2013 30
1 row selected.
rajesh@ORA11G> select *
2 from (
3 select trunc(created,'mm') dt,count(*) cnt
4 from all_users
5 where created between to_date(:b1,'dd-mon-yyyy')
6 and to_date(:b2,'dd-mon-yyyy')
7 group by trunc(created,'mm')
8 )
9 model
10 dimension by (dt)
11 measures( cnt )
12 rules
13 ( cnt[ for dt from to_date(:b1,'dd-mon-yyyy')
14 to to_date(:b2,'dd-mon-yyyy')
15 increment numtoyminterval(1,'month') ] = nvl(cnt[cv()] ,0) )
16 order by 1
17 /
DT CNT
----------- ----------
01-JAN-2013 0
01-FEB-2013 0
01-MAR-2013 0
01-APR-2013 0
01-MAY-2013 0
01-JUN-2013 0
01-JUL-2013 0
01-AUG-2013 0
01-SEP-2013 0
01-OCT-2013 30
01-NOV-2013 0
01-DEC-2013 0
12 rows selected.
rajesh@ORA11G>