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

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.