User Tools

Site Tools


kurs:create_calendar

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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>​
kurs/create_calendar.txt ยท Last modified: 2014/09/10 21:22 (external edit)