Hi Ryan. That's a common issue for reporting. This post might have you
an idea where to generate those inexistent dates (time slots), just
forget about the specific aggregates and partitioning done in there:
DRI, Consultoria Informática
Telef: +351 936 184 086
Shawn Green (MySQL) escreveu:
> On 1/10/2011 18:51, Ryan Liu wrote:
>> In MySQL, is that possible to generate a data set join with it on fly
>> create an temporary table)?
>> e.g. for a report used by a graphic tool, it requires data in all
>> dates, even it
>> is null. Can I
>> select vacationT.* left join ( all dates d in the past 3 years) on
>> vacationT.`date` = d ?
> Sorry, no. To do the report I think you are describing will require you
> to have a table of all dates. Also the date table needs to be on the
> LEFT side of the LEFT JOIN to be included even if there aren't any matches.
> SELECT ....
> FROM master_date_table LEFT JOIN vacationT ...
> Or, you can accept the partial list of dates actually stored in the
> database as accurate and fill in any missing dates when you render it in
> your report (inside the application). It may be much easier to fill-in
> those dates when you format the report, have you checked?