set echo on
create table sales
as
select 'The Da Vinci Code' book, date '2005-03-01' month, 'Netherlands' country, 5 amount from dual union all
select 'The Da Vinci Code', date '2005-04-01', 'Netherlands', 8 from dual union all
select 'The Da Vinci Code', date '2005-05-01', 'Netherlands', 3 from dual union all
select 'The Da Vinci Code', date '2005-07-01', 'Netherlands', 2 from dual union all
select 'The Da Vinci Code', date '2005-10-01', 'Netherlands', 1 from dual union all
select 'The Da Vinci Code', date '2005-02-01', 'United Kingdom', 15 from dual union all
select 'The Da Vinci Code', date '2005-03-01', 'United Kingdom', 33 from dual union all
select 'The Da Vinci Code', date '2005-04-01', 'United Kingdom', 47 from dual union all
select 'The Da Vinci Code', date '2005-05-01', 'United Kingdom', 44 from dual union all
select 'The Da Vinci Code', date '2005-06-01', 'United Kingdom', 11 from dual union all
select 'The Da Vinci Code', date '2005-08-01', 'United Kingdom', 2 from dual union all
select 'The Da Vinci Code', date '2005-05-01', 'France', 2 from dual union all
select 'The Da Vinci Code', date '2005-08-01', 'France', 3 from dual union all
select 'The Da Vinci Code', date '2006-01-01', 'France', 4 from dual union all
select 'Bosatlas', date '2005-01-01', 'Netherlands', 102 from dual union all
select 'Bosatlas', date '2005-02-01', 'Netherlands', 55 from dual union all
select 'Bosatlas', date '2005-03-01', 'Netherlands', 68 from dual union all
select 'Bosatlas', date '2005-04-01', 'Netherlands', 42 from dual union all
select 'Bosatlas', date '2005-05-01', 'Netherlands', 87 from dual union all
select 'Bosatlas', date '2005-06-01', 'Netherlands', 40 from dual union all
select 'Bosatlas', date '2005-07-01', 'Netherlands', 31 from dual union all
select 'Bosatlas', date '2005-08-01', 'Netherlands', 26 from dual union all
select 'Bosatlas', date '2005-09-01', 'Netherlands', 22 from dual union all
select 'Bosatlas', date '2005-10-01', 'Netherlands', 23 from dual union all
select 'Bosatlas', date '2005-11-01', 'Netherlands', 88 from dual union all
select 'Bosatlas', date '2005-12-01', 'Netherlands', 143 from dual union all
select 'Bosatlas', date '2006-01-01', 'Netherlands', 31 from dual union all
select 'Bosatlas', date '2006-02-01', 'Netherlands', 18 from dual union all
select 'Bosatlas', date '2006-03-01', 'Netherlands', 15 from dual union all
select 'Bosatlas', date '2006-04-01', 'Netherlands', 11 from dual union all
select 'Bosatlas', date '2006-05-01', 'Netherlands', 17 from dual union all
select 'Bosatlas', date '2006-06-01', 'Netherlands', 9 from dual union all
select 'Bosatlas', date '2006-07-01', 'Netherlands', 12 from dual union all
select 'Bosatlas', date '2006-08-01', 'Netherlands', 20 from dual union all
select 'Bosatlas', date '2006-09-01', 'Netherlands', 4 from dual union all
select 'Bosatlas', date '2006-10-01', 'Netherlands', 5 from dual union all
select 'Bosatlas', date '2006-11-01', 'Netherlands', 1 from dual union all
select 'Bosatlas', date '2006-12-01', 'Netherlands', 1 from dual
/
alter session set nls_date_format = 'fmmonth yyyy'
/
select book
     , month
     , country
     , amount
  from sales
 model
       return updated rows
       partition by (country)
       dimension by (book,month)
       measures (amount)
       rules
       ( amount['Bosatlas',month > date '2006-06-01'] =
           amount['Bosatlas',cv(month)] * 2
       )
/
select book
     , month
     , country
     , amount
  from sales
 model
       return updated rows
       partition by (country)
       dimension by (book, month)
       measures (amount)
       rules
       ( amount[any,any] = cv()
       )
 order by book, month
/
select book
     , month
     , country
     , amount
  from sales
 model
       return updated rows
       partition by (country)
       dimension by (book, month)
       measures (amount)
       rules
       ( amount[any,date '2005-08-01'] = 200
       )
 order by book, month
/
create table prices
as
select 'Bosatlas' book, 42.95 price from dual union all
select 'The Da Vinci Code', 19.95 from dual
/
select book
     , month
     , country
     , amount
     , to_char(turnover,'99G990D00') turnover
  from sales
 where month between date '2005-07-01' and date '2005-12-31'
 model
       reference prices on (select book, price from prices)
         dimension by (book)
         measures (price)
       main resultaat
         partition by (country)
         dimension by (book, month)
         measures (0 as turnover, amount)
         rules
         ( turnover[any,any] = amount[cv(),cv()] * price[cv(book)]
         )
 order by book
     , month
/
select book
     , month
     , country
     , a1 amount
     , a2 amount_to_the_fourth
  from sales
 where book = 'The Da Vinci Code'
   and country = 'Netherlands'
 model
       partition by (country)
       dimension by (book,month)
       measures (amount a1, amount a2)
       rules iterate (3)
       ( a2[any,any] = a2[cv(),cv()] * a1[cv(),cv()]
       )
 order by month
/
select book
     , country
     , to_date(to_char(y) || to_char(m),'yyyymm') month
     , amount
  from sales
 where book = 'Bosatlas'
   and extract (year from month) = 2006
 model
       partition by ( book, country)
       dimension by ( extract(year from month) y, extract(month from month) m)
       measures (amount, 0 max_monthly_amount)
       rules upsert all
       iterate (100) until (max_monthly_amount[2007+iteration_number,1] < 4)
       ( amount[2007+iteration_number,any]
         = trunc(amount[2006+iteration_number,cv()]/4)
       , max_monthly_amount[2007+iteration_number,1]
         = max(amount)[2007+iteration_number,m between 1 and 12]
       )
 order by y, m
/
set echo off
drop table prices
/
drop table sales
/