Yeah it is confusing huh?
Lets say the data looked like this: - so sorted by id_number (low to high) - and date (most recent to oldest)
id_number show_date attendance
(1, '12/01/2016', 4000),
(1, '11/04/2016', 3000),
(1, '08/04/2016', 2000),
(1, '01/05/2016', 9500),
(1, '08/01/2015', 4200),
(1, '01/05/2015', 3800),
(1, '07/15/2014', 3800),
(2, '03/01/2016', 2000),
(2, '02/04/2015', 4000),
(2, '01/01/2014', 4800),
(2, '12/18/2013', 1000),
(2, '12/18/2012', 7000)
(3, '12/01/2016', 4000),
(3, '11/04/2016', 3000),
(3, '10/04/2016', 6000),
(3, '01/05/2016', 1500),
(3, '08/01/2015', 4200),
(3, '01/05/2015', 3800),
...so what should be returned is:
(1, '12/01/2016', 4000)
(2, '03/01/2016', 2000)
For id_number 1:
Only looking at the first 3, because the 4th one is beyond attendance is 5000. So out of the 3 remaining, does the time-span exceed 90 days? It does, so then I want to return the first record (the one with the most recent date)
For id_number 2:
Only looking at the first 4, because the 4th one is beyond attendance is 5000. So out of the 4 remaining, does the time-span exceed 90 days? It does, so then I want to return the first record (the one with the most recent date)
...so I want to check the time-spans between the most recent one, and the oldest one on the list that didn't exceed 5000.
For id_number 3:
Only looking at the first 2, because the 3rd one is beyond attendance is 5000. Since the remaining two don't have a time-span that exceeds 90 days I don't want to return anything.
ICQ 10556 (ya), 254117