User Tools

Site Tools


kurs:create_calendar

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision Both sides next revision
kurs:create_calendar [2008/12/04 15:57]
mh
kurs:create_calendar [2008/12/04 16:25]
mh
Line 1: Line 1:
 +<​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)