January 7th, 2013, 10:15 AM
Join Date: Mar 2011
Time spent in forums: 9 h 39 m 50 sec
Reputation Power: 3
Correct use of calendar table to fill gaps
I have a table with a productID, date and revenue column.
I want to return the revenue per date between 2012-01-01 and 2012-06-30 for a specific productID.
There is no revenue data between 2012-01-01 and 2012-03-15 but I want rows returned for those NULL dates anyway.
I'm using a calendar table to do this but I can't find the correct way to join them.
RIGHT JOIN revenue
ON revenue.date = calendar.datefield
WHERE (calendar.datefield BETWEEN '2012-01-01' AND '2012-06-30')
AND (revenue.productID = 'A'
OR revenue.totalrevenue = ''
OR revenue.totalrevenue IS NULL);
The above returns all the dates and revenues where revenue exists, but not the 'blank' dates.
I think this is to do with understanding what side of the query needs to allow nulls, or something - but maybe not!
Any assistance would be great appreciated.