OK, not sure if this is "the textbook" way to solve this problem, but this *should* give you a correct result and a *very* fast return, if I'm not mistaken. What I've done is run INNER JOINs to get the "real" reports, and UNIONed a smaller, LEFT JOINed resultset for the "fake" reports... Tell me whether this will work for you:
(
SELECT PR.ReportDate
, PR.WorkDay
, PRS.ShiftName
, SUM((PRU.UnitsCompleted/JR.TotalUnits)*B.Price) AS TotalPrice
, PR.ProductionReportID
, (SUM((PRU.UnitsCompleted/JR.TotalUnits)*B.Price)/(PR.NumSawyers +
PR.NumBuilders + PR.NumForkliftDrivers + PR.NumShipping)/(PR.NumHours)) AS HourlyRate
FROM ProductionReport PR
INNER JOIN ProductionReport_Shift PRS
ON PR.ProductionReport_ShiftID = PRS.ProductionReport_ShiftID
AND PRS.ProductionLocationID = '2'
INNER JOIN ProductionReport_Unit PRU
ON PR.ProductionReportID = PRU.ProductionReportID
INNER JOIN JobRelease JR
ON PRU.JobReleaseID = JR.JobReleaseID
INNER JOIN Job J
ON JR.JobID = J.JobID
INNER JOIN Batch B
ON JR.JobReleaseID = B.JobReleaseID
WHERE PR.ReportDate BETWEEN '2005-12-01' AND '2005-12-31'
GROUP BY
PR.ProductionReport_ShiftID
, PR.ReportDate
)
UNION ALL
(
SELECT PR.ReportDate
, PR.WorkDay
, PRS.ShiftName
, NULL AS TotalPrice
, PR.ProductionReportID
, NULL AS HourlyRate
FROM ProductionReport PR
INNER JOIN ProductionReport_Shift PRS
ON PR.ProductionReport_ShiftID = PRS.ProductionReport_ShiftID
AND PRS.ProductionLocationID = '2'
LEFT JOIN ProductionReport_Unit PRU
ON PR.ProductionReportID = PRU.ProductionReportID
WHERE PR.ReportDate BETWEEN '2005-12-01' AND '2005-12-31'
AND PRU.ProductionReportID IS NULL # here, we include only the fake reports...
GROUP BY
PR.ProductionReport_ShiftID
, PR.ReportDate
)
ORDER BY ProductionReportID;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com
Edited 1 time(s). Last edit at 12/20/2005 04:28PM by Jay Pipes.