Skip to main content
Solved

GI Scenario - can I build it

  • 15 February 2024
  • 1 reply
  • 46 views

Forum|alt.badge.img

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 

Best answer by dcomerford

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.

View original
Did this topic help you find an answer to your question?

1 reply

dcomerford
Captain I
Forum|alt.badge.img+15
  • Captain I
  • 597 replies
  • Answer
  • February 15, 2024

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings