Skip to main content

I have two tables created but unsure how I can join these since I have different conditions & aggregate functions.

 

Our goal is to see the total # of Service hours for an Installs completed last week. Service hours are defined as estimated duration of inventory IDs on SORs that start with “SER”. Ideally table would look as follows:

Service Order Nbr.     Install Appointment #    Install Date   Total Service

 

Here is table 1:

  • The conditions here are FSSODET.InventoryID = Install
  • Grouped by FSServiceOrder.RefNbr
  • The Install Date is the FSAppointmentDet.TranDate and aggregate function set as Max (to give us the latest Install Date.

 

Here is table 2:

  • The condition here is FSSODET.InventoryID starts with “SER”.
  • Grouped by FSServiceOrder.RefNbr
  • The total service is the FSSODet.EstimatedDuration and aggregate function is set to SUM.

 

 

Conclusion:

I’ve tried to combine these two tables but get inconsistencies - I’m unable to sum the FSSODet.EstimatedDuration while omitting the Install item. Not sure whether my approach is working - would love some help 

With different group bys and aggregates you might be better consider a SQL view to do all the maths. Then use this to build a DAC fot your generic inquiry.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved