I have left outer joined resources.resources_id to jobs_has_dispatch.resources_id.
The right table has two date fields (start and end) representing a date range that a particular resource_id is dispatched for.
I am building a calendar style report that will indicate if a resource_id has been dispatched for the calendar date. I have already solved this.
My challenge is: I need to also show on the calendar report the resource_id values that ARE NOT dispatched for the particular date. (I will then highlight them in red so people have a visual of how many unique pieces of equipment (resource_id) are not dispatched in a given month). All resource_id values are in the left table (resources.resources_id) but there isn't a record with a date range in the right table to say when the resource_id is not dispatched.
For example, resource_id "X2" is dispatched from November 1 to November 10, 2012. I can successfully show "X2" on each calendar date of the report Nobember 1 to November 10, 2012. "X2" is also dispatched from November 15 to November 20, 2012. I can successfully show that on the calendar report.
However, I would like to also show resource_id "X2" on each of November 11, 12, 13, 14 even though "X2" has not been dispatched for those dates. I will then highlight those dates in red so the user has a visual of how much red is on the monthly calendar report.
How to I pull "X2" for the November 11, 12, 13, 14 dates if there is no records in the right table?
thanks in advance!