WO - Primary and Secondary Labor Info

Dave Litwin provided me with an amazing workbook that did exactly what I was looking for: a way to show primary and secondary crew for a work order and be able to roll up the amounts to find charged labor by crew.

Well, here is a query that was created based off of the joins that Dave had in his workbook:

select

r.wo_number, r.description as WO_DESCRIPTION, r.building, r.floor, r.room, r.start_date, r.due_date, r.crew, r.wo_status, r.attribute1, r.assigned_to, r.maint_type, r.cp_number, r.craft as REQ_CRAFT, r.curr_est_hours, r.parent_wo_number,

r.pl_number, r.tracking2, r.equipment, r.est_equipment, r.est_hours, r.est_material, r.est_po, r.est_rate, r.estimate,

la.crew as LABOR_CREW, e.craft, la.employee, e.name, e.craft as EMPLOYEE_CRAFT, la.work_date, la.pay_code, la.hours, la.rate, sum(la.hours*la.rate) as LABOR_CHARGES

from

famis_req r, famis_labor la, famis_emp e

where r.wo_number(+)=la.wo_number

and la.employee(+)=e.employee

group by r.wo_number, r.description, r.building, r.floor, r.room, r.start_date, r.due_date, r.crew, r.wo_status, r.attribute1, r.assigned_to, r.maint_type, r.cp_number, r.craft,

r.curr_est_hours, r.parent_wo_number, r.pl_number, r.tracking2, r.equipment, r.est_equipment, r.est_hours, r.est_material, r.est_po, r.est_rate, r.estimate,

la.crew, e.craft, la.employee, e.name, e.craft, la.work_date, la.pay_code, la.hours, la.rate

The query has been tested and the numbers match what is in Famis when you search for a specific WO.  If you use the query and you find that it is not working please contact me (jenny.knauer@austin.utexas.edu).