Page 1 of 1

how to fill up the missing dates ??

Posted: Sat Jan 10, 2009 11:55 am
by 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.

Re: how to fill up the missing dates ??

Posted: Sat Jan 10, 2009 1:55 pm
by sergio-pro
Hi

Here is how you can do it:

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)

Code: Select all

 
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')
 
Ask if you need more details on this query

Re: how to fill up the missing dates ??

Posted: Sun Jan 11, 2009 10:26 am
by PHPycho
Thanks a lot Mr. sergio-pro!!
That solution was really awesome. Hats off to you :-)