GI Scenario - can I build it

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