我如何才能检索 Exact Online 商店订单中预算的工时与实际完成的工时的组合?
exactonlinerest..MfgTimeTransactions
我可以使用(实际)和exactonlinerest..ShopOrderRoutingStepPlans
(预算小时数)查询分别检索它们吗?
答案1
您可以使用左外连接来合并这些数据。有时也可能需要全外连接,但在这种情况下,您只能在实际有路由步骤时在车间订单上登记时间。
询问:
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