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
