Peter, your solution worked. Thanks a lot!
I had to spin it a bit because the left join..union..right join statement did not work like I expected. It returned datasets for both the Actuals and Projections, I could select on both the OrderDate and the BudgetDate but I could not select both the Actuals and the Projections together on DimDate which is really what I wanted/needed.
I wish I could understand why it did not work, as it looks good to me. But I end up using the following(temp = S_sub_tot_sales):
FROM `dim_dates` dim_dates LEFT OUTER JOIN `S_projections` S_projections ON dim_dates.`date` = S_projections.`BudgetDate`
INNER JOIN `S_sub_tot_sales` S_sub_tot_sales ON dim_dates.`date` = S_sub_tot_sales.`OrdersDate`
UNION
FROM
`dim_dates` dim_dates INNER JOIN `S_projections` S_projections ON dim_dates.`date` = S_projections.`BudgetDate`
LEFT OUTER JOIN `S_sub_tot_sales` S_sub_tot_sales ON dim_dates.`date` = S_sub_tot_sales.`OrdersDate`
The only caveat to this is that the temp table S_sub_tot_sales has to be updated. Thats not really a problem (just another table to cron) but it defeats real-time reporting, charting etc.
So I'm wondering if it is possible to pull the data from the original tables with join..union..join(without creating a temp table) or with a subquery? Thanks.
Regards,
ricky-rich