Displaying data when none exists

[megamef]

Hi All,

I'm making a program that displays database entries between 2 dates in a graph form. I want a new graph entry for every day even if there is no data on that day. for example, If a set the date range to: 08/01/17 to 12/01/17 and I have 2 records between those dates on the 8th and the 10th then I'd get 2 results:

DATE ¦ VALUE
08/01/17 ¦ 25
10/01/17 ¦ 15

what I want is to return this sort of result:

DATE ¦ VALUE
08/01/17 ¦ 25
09/01/17 ¦ 0
10/01/17 ¦ 15
11/01/17 ¦ 0
12/01/17 ¦ 0

What sort of query should I write in order to return the blank results even though those records don't exist in my table?

Cheers

Steve

 

[Ranman256]

you need to make a 'report' table.

run Q1 to append ALL possible dates that can exist to the table. (no values)
I sometimes use a vb loop to add dates from StartDate to EndDate.

run Q2 , update tReport from the data that exists, to fill in existing values.
graph on this table

 

[CJ_London]

you'll need a left join from a table with all the dates you want to display. This may exist in another table or you'll need to create one e.g.

tblDates
calDate date

01/01/2017
01/02/2017
01/03/2017
...
...

then your query would be something like

SELECT d.calDate, q.value
FROM tblDates d LEFT JOIN myQuery q ON d.caldate=q.date
WHERE d.calDate between [start date] and [end date]