This shows you the differences between two versions of the page.
kurs:create_calendar [2010/04/24 15:02] mh |
kurs:create_calendar [2014/09/10 21:22] |
||
---|---|---|---|
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> |