Here is my table structure and data:
CREATE TABLE event (
EventID INT(11) NOT NULL AUTO_INCREMENT,
EventDate DATETIME DEFAULT NULL,
Description VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (EventID)
);
INSERT INTO event (EventID, EventDate, Description) VALUES
(1, '2011-01-01 00:00:00', 'Event 1'),
(2, '2011-03-01 00:00:00', 'Event 2'),
(3, '2011-06-01 00:00:00', 'Event 3'),
(4, '2011-09-01 00:00:00', 'Event 4');
And this query and output:
SELECT *
FROM EVENT
WHERE EventDate BETWEEN '2011-02-01' AND '2011-03-31'
+---------+---------------------+-------------+
| EventID | EventDate | Description |
+---------+---------------------+-------------+
| 2 | 2011-03-01 00:00:00 | Event 2 |
+---------+---------------------+-------------+
1 row in set (0.00 sec)
I want inject empty dates in the result like so:
+---------+---------------------+-------------+
| EventID | EventDate | Description |
+---------+---------------------+-------------+
| NULL | 2011-02-01 00:00:00 | NULL |
| NULL | 2011-02-02 00:00:00 | NULL |
| NULL | 2011-02-03 00:00:00 | NULL |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL | 2011-02-28 00:00:00 | NULL |
| 2 | 2011-03-01 00:00:00 | Event 2 |
| NULL | 2011-03-02 00:00:00 | NULL |
∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨∨
∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧∧
| NULL | 2011-03-31 00:00:00 | NULL |
+---------+---------------------+-------------+
The output should contain 59 rows: 28 for February and 31 for March.
I'll want to avoid any hard coding; instead, I need a very flexible solution that adapts itself to the specified dates.
maybe you need an pivot table, Take a look at that
pivot table schema
name: pivot columns: {i : datatype int}
Populate
create foo table
schema foo
name: foo column: value datatype varchar
your query
An auxiliary calendar table will work well. The simplest possible calendar table is a single column of dates.
You can use a spreadsheet or SQL to populate it. An outer join on it will bring in the dates that don't exist in your data. Limit permissions with GRANT and REVOKE, and use whatever means necessary to make sure that the dates you expect to be in there are actually in there. I run a daily report on my server that makes sure there are 'n' rows, and verifies the earliest and latest dates.
On some platforms, you can generate a series of dates on the fly, and either use it directly or in a CTE. PostgreSQL has functions for that; I don't know whether MySQL does. They're not hard to write, though, if you want to roll your own.
I'm fairly sure this is impossible in pure SQL, so your choices are: