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

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:

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

add a composite index on (date_resolved,product_platform)

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