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:
![](https://uploads-us-west-2.insided.com/acumatica-en/attachment/95319701-d2ec-4036-94cf-67b33ff6ba05.png)
- 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:
![](https://uploads-us-west-2.insided.com/acumatica-en/attachment/832b960c-b137-4b27-9153-ceb84781a334.png)
- 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
Best answer by dcomerford
View original