Three comments on the query:
1. Instead of comparing 'yyyy-mm' strings against 'yyyy-mm-dd hh:mm:ss' strings, why not just write ...
DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN '2012-07' AND '2012-08' ?
2. Since 5.0.12 one should not mix comma joins and explicit joins.
3. It's probably more efficient to test the date conditions in the JOINs.
Those changes give ...
SELECT SQL_CALC_FOUND_ROWS
"---" AS CentreName,
NetworkType,
FundingStateID,
SUM(RegsTarget) AS RegsProfile,
SUM(goOnRegistrationCount) AS goonRegs,
SUM(goOnOLBStartsCount) AS goonStarts,
SUM(ComplsTarget) AS ComplsProfile,
SUM(goOnOLBLADCount) AS goonComps,
DATE_FORMAT(dateReference, "%Y-%m") AS dateReference,
"---" AS CNA_ID
FROM ukon_dw3.persistentNetworkType AS nt
JOIN ukon_dw2.currentCentreFunding AS cf ON cf.NetworkTypeID = nt.NetworkTypeID
JOIN uko_2.hh_Centre AS c ON c.CentreID = cf.CNA_ID
JOIN ukon_dw2.centresDataByDay AS cd
ON cf.CNA_ID = cd.CNA_ID
AND cd.dateReference BETWEEN "2012-07-01" AND "2012-08-17"
AND cd.dateReference BETWEEN p.ProfileEffectiveFrom AND p.ProfileEffectiveTo
LEFT JOIN ukon_dw3.NewpersistentCentreProfile AS p
ON cd.CNA_ID = p.CNA_ID
AND DATE_FORMAT(p.YearMonth,'%Y-%m') BETWEEN '2012-07' AND '2012-08'
WHERE cf.NetworkTypeID=3
AND p.ProfileActive=1
AND p.CourseID=1
GROUP BY DATE_FORMAT(dateReference, "%Y-%m"), NetworkType, FundingStateID
which reveals what looks like redundant date scoping in the cd-cf join, but I don't know the data so I can't resolve the redundancy.
I do not understand:
Quote
The cd table possesses a compound key that hold the date i.e 365 record for each centre.
The cf table doesn't possess a daily date only a centre (CNA_ID) and startDatetime colum.
Hence I can't get a good join.