Jump to content

Select sum related records


This topic is 2520 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

  • 2 months later...

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.

Link to comment
Share on other sites

This topic is 2520 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.