I need a template using SQL to have the first column be each date in a date range including those without data for that date

Question

I am not even sure this is possible but here goes. I am developing a report that shows units into the dock and units received into Oracle. I need to show each day (every day) how many were received at the dock and how many were received into Oracle. The trick is including the days where nothing was received at all.

Date          | Dock Count | Oracle Count
------------------------------------------
Monday 11/1   |     12     |   10
Tuesday 11/2  |     5      |   7
Wednesday 11/3|     0      |   0
Thursday 11/4 |     22     |   10
Friday 11/5   |     0      |   12

So, is there a way to do this? I thought about taking a list of dates from another table that I know has data for each day but that just seems like an inefficient way of doing this.

Note: I am using MySQL

You might want to create a seperate table to hold all the dates instead of using the existing table but essentially, it *will* boil down to that. — Lieven Keersmaekers, Nov 24 '10 at 14:55
Shoot, I was hoping there was a better way. Thanks, I'll look into the date table. — Geoff, Nov 24 '10 at 15:22

score 1 · Answer 1 · edited May 23 '17 at 11:55

Due to Lieven's comment I realize this is inevitably not possible at the current time. Referencing another table that has all of the required dates is the only way. Therefore I searched around as I was sure I wasn't the only one looking to create a table like this.

The current answer to this issue was that I edited the procedure provided in the response to this user's question Get a list of dates between two dates

I am only placing this as an answer in the event someone else searches the database having the same problem I did.

I need a template using SQL to have the first column be each date in a date range including those without data for that date

1 Answers1