SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to fill up the missing dates ??

    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.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

    Code MySQL:
    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

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes there is and it scales better, see the link i posted above or in the other forums you posted this question.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •