This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:create_calendar [2008/12/04 15:57] mh created |
kurs:create_calendar [2010/04/24 15:02] mh |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | Monatskalender beginnend mit 2008 | ||
+ | <code> | ||
+ | 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 | ||
+ | </code> | ||
+ | <code> | ||
+ | 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 | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | 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 | ||
+ | </code> |