January 23, 2003 at 12:35 pm
Hello Everybody--
This view is the basis for some aggregate reporting. It picks the best single value when there are multiple permutations. It returns just under 2000 records in about 3 seconds. Functionally, each combination of OccupancyID and y (year) are unique.
Even simple joins made to this view take several minutes to run. I am guessing that this is because the Query Optimizer is looking into the view to build its performance plan. I want to treat the view as if it were a table. How can I accomplish this?
Create VIEW lv_BestWorkOrderPerYear
AS
SELECT AllWOs.OccupancyID, AllWOs.y, bestWOID = COALESCE(Pass.maxWOID, NoPass.maxWOID)
FROM
(--List of all occupancy/workorder/year combinations
SELECT DISTINCT wo.occupancyID, wo.workOrderID, y = year(firstWorkDate)
FROM tblWorkOrders wo
WHERE firstWorkDate IS NOT NULL
)AllWOs
LEFT JOIN
(--Most Recent WorkOrder for each year (with a pass)
SELECT wo.occupancyID,
y = year(FirstWorkDate),
maxWOID = Max(wo.workOrderID)
FROM tblWorkOrders wo join tblSiteInspections si
ON wo.workOrderID = si.workOrderID
WHERE FirstWorkDate IS NOT NULL
GROUP BY wo.occupancyID, year(FirstWorkDate)
HAVING MIN(Result)= 'A')Pass
ON (AllWOs.occupancyID = Pass.occupancyID AND AllWOs.y = Pass.y)
LEFT JOIN
(--Most Recent WorkOrder for each year (without a pass)
SELECT wo.occupancyID,
y = year(FirstWorkDate),
maxWOID = Max(wo.workOrderID)
FROM tblWorkOrders wo join tblSiteInspections si
on wo.workOrderID = si.workOrderID
WHERE FirstWorkDate IS NOT NULL
GROUP BY wo.occupancyID, year(FirstWorkDate)
HAVING MIN(Result)<> 'A')NoPass
ON (AllWOs.occupancyID = NoPass.occupancyID AND AllWOs.y = NoPass.y)
January 27, 2003 at 8:00 am
This was removed by the editor as SPAM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply