Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Help with returning all dates

    Hi,

    My intention is for this query to return dates even if it is empty.

    select FROM_DAYS(TO_DAYS(date)) as dt, count(*) as no FROM days
    left join tcgaz_data on date = DATE_FORMAT(date_resolved, '%Y-%m-%d')
    WHERE date >= CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-%d')) - INTERVAL 20 DAY
    and product_platform in ('0', 'CSP')
    group by FROM_DAYS(TO_DAYS(date))
    order by dt

    It is skipping the dates from the days table where there is not a value in the data table.

    dt no
    2009-08-17 2
    2009-08-18 4
    2009-08-19 5
    2009-08-20 1
    2009-08-21 2
    2009-08-25 2
    2009-08-26 10
    2009-08-27 1
    2009-09-02 1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since you write columns in your SQL without qualifying which table they came from (not best practice), it's hard to figure out what you're doing, and we have to guess

    for instance, i'll bet the problem is due to the fact that product_platform is actually in the tcgaz_data table, which would explain why the LEFT OUTER JOIN isn't working

    here's my guess at a fix:
    Code:
    SELECT days.date as dt
         , COUNT(tcgaz_data.date_resolved) as no 
      FROM days 
    LEFT OUTER
      JOIN tcgaz_data 
        ON tcgaz_data.date_resolved >= days.date
       AND tcgaz_data.date_resolved  < days.date + INTERVAL 1 DAY
       AND tcgaz_data.product_platform in ('0', 'CSP')
     WHERE days.date >= CURRENT_DATE - INTERVAL 20 DAY
    GROUP 
        BY days.date
    ORDER 
        BY days.date

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    Hi r937

    Looks like your assumptions were correct

    days table is just a list of days in the following format
    2009-09-04 going back 10 years and forward one year

    I created that table just to be able to use the join since that is the only way I could think of to get the display of empty date values.


    the tcgaz_data is a large table with 100,000 rows and 40 K worth of data. (not huge). The table has values for some days and not others.


    The query suggested never comes back with a result after more than 30 minutes.

    Any additional information I can provide or ways to solve my problem?

    Thanks,
    Jim

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add a composite index on (date_resolved,product_platform)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Or reverse those as (product_platform,date_resolved) should allow you to match better on the index. Another item that we have done in the past is to create a user function that creates a table of all dates within a range of dates. This allows you to equate to the date, rather than using greater than/less than.
    Dave

Posting Permissions

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