how to fill up the missing dates ??

[PHPycho]

hello forums!!
I had the table1 as follows:

-----------------
| table1 |
-----------------
| id | visit_date | no_of_hits |
| 1 | 2002-02-12 | 3 |
| 2 | 2002-02-13 | 10 |
| 3 | 2002-02-15 | 200 |
:
:
| n-1| 2009-01-8 | 125 |
| n | 2009-01-10 | 400 |

Now i would like to show all dates between two date ranges (for example 2003-02-12 to 2008-12-30) which
may or may not have no of hits.
I know this can be done by using third table (the table that has all the dates) and using it as left join to table1.

I have seen some tutorials regarding sequence generation that is by using integers table as:

----------
|integers|
----------
|i |
----
|0 |
|1 |
|2 |
:
:
|9 |

We know we can generate sequence of numbers using integers table using the concept of cross joins.
How this concept can be used in my case ie generating all dates between two date range. or any other good method will do too.
Note: the two date range is not fixed as user has option to select any date.
Waiting for the right solutions.
Thank You.

[guelphdad]

http://guelphdad.wefixtech.co.uk/sql...ndarfill.shtml

[PHPycho]

Here is the one solution found so far:
in this example there are three additional tables:
day_index - containing days (1-31)
month_index - containing months (1-12)
year_index - containig years you use (eg 1980-2020)
you can actually substitute day_index and month_index with one table year_day_index (1-356) and constructing date with MAKEDATE(YEAR, DAY_OF_THE_YEAR)

SELECT h.`no_of_hits`, dates.date_val
FROM table1 h
RIGHT OUTER JOIN
(
  SELECT yi.num `y`, mi.num `m`, di.num `d`,
  DATE(CONCAT_WS('-', yi.num, mi.num, di.num)) date_val
  FROM year_index yi, month_index mi, day_index di
  WHERE yi.num BETWEEN 2008 AND 2009
    AND mi.num BETWEEN 1 AND 12
    AND di.`num` BETWEEN 1 AND 31
    AND DATE(CONCAT_WS('-', yi.num, mi.num, di.num))  IS NOT NULL
    ORDER BY y, m, d
) as dates
ON h.`visit_date` = dates.date_val
WHERE dates.date_val BETWEEN DATE('2008-02-21') AND DATE('2009-02-02')

This code works fine. Is there any alternatives besides this.
Thank you

[guelphdad]

yes there is and it scales better, see the link i posted above or in the other forums you posted this question.