Double self join
This problem drives me mad. I have a table with a date field D. My records have to be grouped in series. A new serie starts when there is no previous record in the last 365 days before the date of the current record. A serie ends 183 days after it started.
The first part was not so difficult. I made two table occurrences, T1 and T_Previous that were joined by
T1::D > T_Previous::D
Then I added two calculation fields.
PreviousDate Calculation From T1, = Max ( T_Previous::D )
IsFirstOfSerie Calculation From T1, = IsEmpty ( PreviousDate ) or ( ( D - PreviousDate) > 365 )
To explain what I think this accomplishes: In T_Previous I get all the dates before the current record in T1. PreviousDate will be the last of those previous dates. If there is no such previous date, or the previous date is more than 365 days ago, the current record in T_First is the first of a serie. It seems to work.
For the second part I made two table occurrences, T2 and T_First, that were joined by
T2::D >= T_First::D
and T2::z_True = T_First::IsFirstOfSerie
(z_True is a global field that is always True.)
FirstDateOfLastSerie Calculation From T2, = Max ( T_First::D )
IsPartOfSerie Calculation From T2, = ( D - FirstDateOfLastSerie ) < 183
From the context of T2 I hope to find in T_First the dates that are older than (or equal to) the current date in T2 and that are the first of a serie. FirstDateOfLastSerie should be the last of those dates and thus the start of the latest serie. If the current record is within 183 days of the start of of the last serie, it is part of that serie. This step fails miserably.
In the end I solved this by using ExecuteSQL() in stead of table occurrences. I have no idea whether that has any impact on performance. And I would really like to understand how to get the Filemaker approach with table occurrences working.
PreviousDate Calculation _SQLDate2Date ( ExecuteSQL ( "select max ( D ) from T1 where D < ? " ; "" ; "" ; T1::D ) )
FirstDateOfLastSerie Calculation _SQLDate2Date ( ExecuteSQL ( "select max ( D ) From T1 where D <= ? and IsFirstOfSerie = 1" ; "" ; "" ; T1::D ) )
(_SQLDate2Date is a custom function that converts the SQL date format to a Filemaker date,)
Thanks in advance for any help.