Create numbers and calendar tables

-- Numbers and Calendar tables to make certain queries easier
-- See: http://stackoverflow.com/a/5123255

use dbMain;

-- create digits table
create table digits(n int);
insert into digits values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table numbers(
   n int not null
  ,primary key(n)
);


-- create numbers table
insert
  into numbers(n)
select 1
      + (d1.n * 1)
      + (d2.n * 10)
      + (d3.n * 100)
      + (d4.n * 1000)
      + (d5.n * 10000)
      + (d6.n * 100000) as n
  from digits d1
      ,digits d2
      ,digits d3
      ,digits d4
      ,digits d5
      ,digits d6;

-- drop the working table
drop table digits;


-- create calendar table
create table calendar(
   datum date not null
  ,primary key(datum)
);


-- populate calendar table
insert
  into calendar(datum)
select date_add('1999-12-31', interval n day)
  from numbers
 where n <=8000;