Select sum related records

[sal88]

Hi all

I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:

Clients
Client_ID, Seats
Jobs
Client_IDF, Job_time, Job_Date

The best I've managed so far is to find the distinct Client_IDF from all Jobs:

ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J
WHERE j.Log_date >= ?"; ""; "";  $date_30_days_ago)

And then loop through the list and perform the 'divide by seats' calculation for each line

Or to do something similar but from the Clients table:

ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C
JOIN Jobs J ON j.log_client_IDF = c.Client_ID
WHERE l.Log_date >= ?"; ""; "";  $date_30_days_ago)

But no joy in either case when it comes to actually performing calculations within SQL.

Is this possible in FM SQL?

Many thanks

[beverly]

the problem may well come from limitations in the ExecuteSQL function. Nested functions with the aggregates ( SUM, COUNT, etc.) don't always work as expected.

You may well do the SUM as a caclulation in Clients (use ExecuteSQL) to get the time in the last 30 days.

Or use a Let() statement to get the sum of the time (no join needed) in the last 30 days and push to a variable that can be then divided by the Seats. Use this field to FIND the Client with the "most".

I probably would NOT use a calculation (unstored), but a looping script to Set Field.