This will return monthly work order cost for labor, material, and tools for all equipment or for the selected equipment for the last 12 full months plus the current month.
SELECT TO_CHAR(evt_completed,'YYYY-MM') as "Month",
SUM(evo_labor) as "Labor",
SUM(evo_material) as "Material",
SUM(evo_tool) as "Tool"
FROM r5events, r5eventcost
WHERE evt_code = evo_event
AND evt_completed >= ADD_MONTHS( LAST_DAY( TRUNC( SYSDATE ) ) + 1, - 13 )
AND evt_object LIKE COALESCE( :OBJECT , '%' )
AND evt_object_org LIKE COALESCE( :OBJECTORG , '%' )
GROUP BY TO_CHAR( evt_completed , 'YYYY-MM' )
ORDER BY 1
This will return yearly work order cost for labor and material for all equipment or for the selected equipment for the last 2 full years plus the current year.
SELECT TO_NUMBER(TO_CHAR(evt_completed,'YYYY')) as "Year",
SUM(evo_labor) as "Labor",
SUM(evo_material) as "Material"
FROM r5events, r5eventcost
WHERE evt_code = evo_event
AND evt_completed >= ADD_MONTHS( TRUNC( SYSDATE , 'YEAR' ), -24 )
AND evt_object LIKE COALESCE( :OBJECT , '%' )
AND evt_object_org LIKE COALESCE( :OBJECTORG , '%' )
GROUP BY TO_NUMBER(TO_CHAR(evt_completed,'YYYY'))
ORDER BY 1