dokuwiki

Monatskalender beginnend mit 2008

with 
calendar as (
select datum  from 
 (select to_char(to_date( '20080101' , 'yyyymmdd')+to_yminterval(j||'-'||i), 'yyyymm') as datum from 
 (select level-1 as j from dual connect by level < 4),
 (select level-1 as i from dual connect by level <= 12))
)
select * from calendar

select datum, sum(s) from (
select datum, demo_orders.order_total s from 
(select to_char(to_date( '20080101' , 'yyyymmdd')+to_yminterval(j||'-'||i), 'yyyymm') as datum from 
(select level-1 as j from dual connect by level < 2),
(select level-1 as i from dual connect by level < 13)) left outer join
demo_orders on
datum = to_char(demo_orders.order_timestamp, 'yyyymm')
)
group by datum
order by datum

with calendar as (
select datum  from 
 (select to_char(to_date( '20080101' , 'yyyymmdd')+to_yminterval(j||'-'||i), 'yyyymm') as datum from 
 (select level-1 as j from dual connect by level < 4),
 (select level-1 as i from dual connect by level <= 12))
),
ot as (
select to_char(demo_orders.order_timestamp, 'yyyymm') datum, order_total s from demo_orders
)
select c.datum, sum(s), count(s) from calendar c, ot
where ot.datum(+) = c.datum
group by c.datum
order by c.datum