There are many ways to do this but relying on the WOGroup avoids having to bring the WOAncestor into it. Below are 2 options:
SELECT WO.WONum, WO.WorkType,
(SELECT SUM(ActLabCost) FROM WorkOrder AC WHERE AC.WOGroup = WO.WOGroup) as "Actual Labor Cost",
(SELECT SUM(ActMatCost) FROM WorkOrder AC WHERE AC.WOGroup = WO.WOGroup) as "Actual Material Cost"
FROM WorkOrder WO
WHERE WO.IsTask = 0
ORDER BY WO.WONum
;
This option avoids having to use a GROUP BY but the multiple embedded SELECTs may not perform as well as the next option.
SELECT WO.WONum, MIN(WO.WorkType) as "Work Type",
SUM(AC.ActLabCost) as "Actual Labor Cost",
SUM(AC.ActMatCost) as "Actual Material Cost"
FROM WorkOrder WO
INNER JOIN WorkOrder AC
ON AC.WOGroup = WO.WOGroup
WHERE WO.IsTask = 0
GROUP BY WO.WONum
ORDER BY WO.WONum
;
This option relies on a JOIN between the Non-Task WorkOrder records and all WorkOrder records with the same WOGroup including the Non-Task WO.
Using the "MIN(WO.WorkType)" avoids having to include every field you want to include from the Non-Task WO in the GROUP BY clause.but it may be a bit harder to read. Just using "WO.WorkType" in the main SELECT and repeating all the "WO.Xxx..." columns in the GROUP BY clause accomplishes the same thing.
------------------------------
Julio Hernandez
------------------------------
Original Message:
Sent: Thu November 05, 2020 09:48 PM
From: User1971
Subject: Select WO actuals and include task actuals?
Maximo 7.6.1.2; Oracle 18c
I want to write a SQL query that selects WOs (non-tasks) - and include the costs of their tasks.
The query would mimic the Actuals column in the View Costs window in Work Order Tracking:
Actuals (includes task actuals):
- Labor Costs
- Material Cost
- Tool Cost
- Service Cost
- Total Cost
(WOTRACK --> More Actions --> View --> Costs)
What's the simplest way to select WO actuals (and include the task actuals)?
- I want to select other WO columns too (wonum, division, classificationID, worktype, etc.) which might make GROUPing BY complicated.
- Or are there OOB WORKORDER columns that include rolled-up task actuals?
Thanks!
#AssetandFacilitiesManagement
#Maximo