I am trying to build a report that displays all helpdesk calls logged in the last 30 days. The problem I have is that if a call is not logged on a day then there is blank in my report which looks messy.
So what I have done is created a table of dates from 2010-01-01 to 2030-01-01
I want to join these dates to my real table of dates. I am using the following mysql statement but it is not showing me the missing dates as expected.
datestable.date being the table with my dates
mytable being where the data is stored from my helpdesk.
from datestable LEFT JOIN mytable ON datestable.date = FROM_UNIXTIME(logdatex,"%Y-%d-%m")
WHERE datestable.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE()