Create and fill a calendar table
First create this basic integers table. It will be useful to you in many different applications.
CREATE TABLE integers (i int not null primary key);
INSERT INTO integers VALUES
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
We can use this integers table and CROSS JOINs to quickly return a list of numbers between 1 and 1000 for instance.
Two things to note as we get into the code:
1) a table can be joined to itself through aliases, you can give a table an alias in one instance and use the table name in the other, but best practice is to reference it with an alias each time, that way you will easily see that the table is joined to itself
2) when you join a table to another table (even in a self join), if you leave out a join condition you produce one row from the right hand table for each row in the left hand table. Thus if we cross the integers table with itself we should expect 10 rows created for each row in the integers table, or 100 rows. Cross joining it again would produce 1,000 rows and so on.
So let's produce those 1,000 rows.
SELECT
hundreds.i*100 + tens.i*10 + units.i +1 as ournumbers
FROM
integers as hundreds
CROSS JOIN
integers as tens
CROSS JOIN
integers as units
ORDER BY ournumbers
Simple isn't it? And you can see how that can easily be manipulated to give you even millions of rows right?
Now suppose we wanted to limit the values to a specific range. We could include that range like this:
SELECT * FROM
(
SELECT
hundreds.i*100 + tens.i*10 + units.i +1 as ournumbers
FROM
integers as hundreds
CROSS JOIN
integers as tens
CROSS JOIN
integers as units
) as ourtable
WHERE ournumbers BETWEEN 86 and 94
ORDER BY ournumbers
With those basics down we will move on to filling a calendar table with data. First create the simplest of tables:
CREATE TABLE calendar(
mydate date primary key) // make it a primary key so you don't enter duplicate dates later;
We'll fill it with 100 rows. For those of you not familiar with how DATE_ADD (or its alias ADDDATE) works take a look at it in the manual.
We'll set a start date and save it in a variable:
SET @start = '2007-01-01';
INSERT INTO calendar
SELECT DATE_ADD(@start, interval ournumbers day) as ourdays
FROM
(
SELECT
hundreds.i*100 + tens.i*10 + units.i +1 as ournumbers
FROM
integers as hundreds
CROSS JOIN
integers as tens
CROSS JOIN
integers as units
) as ourtable
WHERE ournumbers BETWEEN 0 and 15
ORDER BY ournumbers
Or if you prefer to have a specific end date you could also set the number of days you want in a variable. You can use DATEDIFF to do the calculation for you. Say you need all dates between Jan 1, 2007 and August 8, 2008
SET @start = '2007-01-01';
SET @end = DATEDIFF('2008-08-08', @start);
INSERT INTO calendar
SELECT DATE_ADD(@start, interval ournumbers day) as ourdays
FROM
(
SELECT
hundreds.i*100 + tens.i*10 + units.i +1 as ournumbers
FROM
integers as hundreds
CROSS JOIN
integers as tens
CROSS JOIN
integers as units
) as ourtable
WHERE ournumbers BETWEEN 0 and @end
ORDER BY ournumbers
Gratitude to Rudy Limeback (http://r937.com) for showing me this method in our various forum meetings.