I have Product records that have two fields:

• SupplierID number field
• Last_Updated date field

I want to group this data by the Supplier and a set of Age categories depending on how long ago the Product was last updated. This will allow me to chart each supplier to see where data needs updating.

For the non-working example, I'll only include two age categories for clarity.
 

SELECT
SupplierID,
CASE WHEN Last_Update < ? THEN 1
WHEN Last_Update > ? AND Last_Update < ? THEN 2 
ELSE 0 END As Age,
Count(*)
FROM Products
WHERE
SupplierID IN (SELECT DISTINCT SupplierID FROM Suppliers)
GROUP BY SupplierID,Age";
"";
"";
Get(CurrentDate)-7);
Get(CurrentDate)-14;
Get(CurrentDate)-7
)

 

As far as I can tell (and I've experimented a lot with different workarounds) it is NOT actually possible to achieve this in Filemaker SQL in one go.

In full fat SQL you cannot GROUP by a column alias as I've shown above, and obviously neither can Filemaker.

A sub query in the SELECT would be one way of doing it, but in Filemaker you can only subquery in WHERE.

Filemaker does not allow Common Table Expressions (CTE), which would be ideal.

Using CASE within GROUP BY doesn't work, e.g

GROUP BY SupplierID,CASE WHEN Last_Updated < ? THEN 1 ELSE 7 END

Can anyone think of any crafty workarounds that don't involve looping through records and doing many SQL calls?

You could use the Fast Summaries method by Mikhail Edoshin. But you would have to add a calculation field to calculate the age group.

Come to think of it, once you've added it, you could also do it using ExecuteSQL(), no?

 

 

Edited September 11, 20214 yr by comment

I am purposefully avoiding calculation fields because our old database ended up filled with them over the years, bringing it to its knees.

The data set, while not huge, is significant enough that a calculation that depends at run-time on the current date to calculate its age would be highly inefficient. I believe Filemaker would need to update all of those calculations every time before the SQL could do a SELECT. I may be wrong.

I can't even SELECT into another temporary table and from there count by SupplierID, because at the very heart of the problem is being able to GROUP BY the CASE of Age.

Very frustrating and so easy to do in proper SQL.

13 minutes ago, V8S said:

The data set, while not huge, is significant enough that a calculation that depends at run-time on the current date to calculate its age would be highly inefficient.

Well, you need to calculate the age group one way or another. I am not sure if there is a significant difference between the two methods.

 

For now I've decided to do one ExecuteSQL that inserts SupplierIDs and an Age_Group into a table, and then do another ExecuteSQL on that to get a COUNT for each Supplier and Age_Group.

But if anyone knows of a more elegant solution....

I forgot to say I'm use Monkeybread.

2 minutes ago, comment said:

Well, you need to calculate the age group one way or another. I am not sure if there is a significant difference between the two methods.

 

A calculation field will always be affecting performance in the table whether I'm using the data or not. Every day the calculation will change as the age grows.

The system I am building will be run by the server overnight, so a huge set of KPIs are available the next day. Having the server take 10 seconds to do this function at night is far superior to having the user and server affected during the day.

5 minutes ago, V8S said:

A calculation field will always be affecting performance in the table whether I'm using the data or not.

No, that is not correct. An unstored calculation field (as this must be, if you want it to rely on current date) evaluates only as needed. If you do not place it on any layout and reference it only in your script, it will evaluate ONLY when you run your script.

 

 

4 minutes ago, comment said:

No, that is not correct. An unstored calculation field (as this must be, if you want it to rely on current date) evaluates only as needed. If you do not place it on any layout and reference it only in your script, it will evaluate ONLY when you run your script.

 

 

I did not know that. Thanks for the heads-up.

I will do some tests to figure out which is faster