How do I find Closest dates in FileMaker using SQL

A definitive way to find the closest match of dates between 2 tables Hi, I have a school project whereby I have to add mathematically derived days to high and low dates and then evaluate the derived turning point to my historical results. I have three tables in FileMaker 19:

HighLowData with a field TurnDate

Maths which has 20 formulae

TurningPoints which is created from script looping the maths formulae with the HighLowDate::TurnDate.

Therefore each HighLowDate::TurnDate results in 20 TurningPoints::cTPdate entries.

I have a field in TurningPoints cLookup in which I wish to calculate the closest TurnDate in HighLowData to my cTPdate.

I have tried to ExecuteSQL ( "SELECT TurnDate FROM HighLowData JOIN TurningPoints ORDER BY ABS (HighLowDate::TurnDate - TurningPoints::cTPdate) FETCH FIRST 1 ROW ONLY"; "";"")

I admit SQL is outside my comfort zone. Is there another way to do the task or can my SQL be tidied up to work? I understand DATEDIFF doesn't work with FileMaker.

I tried LookupNext but that just returned the actual TurnDate for selected row. Thanks in advance

Solution

SQL is fine. I would use a different approach.

Start with

ExecuteSQL
("SELECT cTPdate 
  FROM TurningPoints
  WHERE cTPdate >=?
  ORDER BY cTPdate DESC
  FETCH FIRST 1 ROW ONLY
"; 

"";"";HighLowDate::TurnDate)

This will give you the closest match in the future date

Reverse the query and you will get the closest match in the past

Then you can compare the difference between these two days and your date and get the closest match.

If you need as a calc you can use Let statement