Retrieve list of shop order budgeted man hours and actual made hours

Question

How can I retrieve a combination of the budgeted man hours on an Exact Online shop order with the actual hours made?

I can retrieve them separately using a query on exactonlinerest..MfgTimeTransactions (actuals) and exactonlinerest..ShopOrderRoutingStepPlans (budgeted hours)?

Answer 1

You can combine these data using a left outer join. A full outer join might be needed sometimes too, but in this situation you can only register time on a shoporder when it actually has routing steps.

Query:

select timebgt.shopordernumber
,      timebgt.PlannedTotalHours
,      ttn.man_hours
from   ( select sor.shopordernumber
         ,      son.shoporder
         ,      sum(PlannedTotalHours) PlannedTotalHours 
         from   exactonlinerest..ShopOrderRoutingStepPlans son 
         join   exactonlinerest..shoporders sor 
         on     sor.id = son.shoporder 
         group 
         by     sor.shopordernumber
         ,      son.shoporder
       ) 
       timebgt
left 
outer  
join   ( select mtn.shoporder
         --,      sor.shopordernumber
         --,      sor.description shoporderdescription
         --,      mtn.date
         --,      mtn.activity setup_or_run
         --,      mtn.status hour_status
         ,      sum(mtn.laborhours) man_hours
         --,      emp.birthname man_name
         --,      mtn.hours wcr_hours
         --,      wcr.code wcr_code
         --,      wcr.description wcr_description
         from   exactonlinerest..MfgTimeTransactions mtn
         join   exactonlinerest..Employees emp
         on     emp.id = mtn.employee
         join   exactonlinerest..Workcenters wcr
         on     wcr.id = mtn.workcenter
         join   exactonlinerest..shoporders sor
         on     sor.id = mtn.shoporder
         group 
         by     mtn.shoporder
       ) ttn
on     ttn.shoporder = timebgt.shoporder
order 
by     timebgt.shopordernumber