Forum Moderators: open
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.
SELECT h.`no_of_hits`, dates.date_val
FROM hits 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')
