A placement table with, dateStart, dateEnd, venueUID and personUID.
A person can be 'placed' at a location for anything between 2 weeks and 8 years and at any date
A 'year' starts 01 September and ends 30 july, Year 2010 would be from 01 sept 2010 to 30 July 2011
We want to run a report that lists every person who was at a venue in a year range (2009, 2010, 2011 etc) and show the maximum and minimum number of people who were at that veue on any 1 day in that year.
The first part is fairly straightforward, multiple finds (for each year) bearing in mind that there be no dateEnd and that a person could be in multiple years. I would then use the great http://http://www.modularfilemaker.org/module/hyperlist/ to craete the report.
The problem is getting the maximum and minium number of people who were at that venue at any day of the 'year'. I cannot see any way other than looping through for each date between the end and start dates of the year and seing how many records match, but feel sure there must be a better way. I have very limited knowledge of SQL but cannot see a way that a query could give those results easily.
Any thoughts and suggestions welcome!