>>>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.
>>
>>I know you put the question in the MSSQL forum, but since you mentioned VFP at the beginning, this will work for both (with some adjustment for date difference calculus in MSSQL) and it is a single statement:
>>
>>

>>SET DATE MDY
>>
>>CREATE CURSOR va (id_number I, show_date D, attendance I)
>>
>>INSERT INTO va VALUES (1, CTOD('12/01/2016'), 4000)
>>INSERT INTO va VALUES (1, CTOD('11/04/2016'), 3000)
>>INSERT INTO va VALUES (1, CTOD('08/04/2016'), 2000)
>>INSERT INTO va VALUES (1, CTOD('01/05/2016'), 9500)
>>INSERT INTO va VALUES (1, CTOD('08/01/2015'), 4200)
>>INSERT INTO va VALUES (1, CTOD('01/05/2015'), 3800)
>>INSERT INTO va VALUES (1, CTOD('07/15/2014'), 3800)
>>INSERT INTO va VALUES (2, CTOD('03/01/2016'), 2000)
>>INSERT INTO va VALUES (2, CTOD('02/04/2015'), 4000)
>>INSERT INTO va VALUES (2, CTOD('01/01/2014'), 4800)
>>INSERT INTO va VALUES (2, CTOD('12/18/2013'), 1000)
>>INSERT INTO va VALUES (2, CTOD('12/18/2012'), 7000)
>>INSERT INTO va VALUES (3, CTOD('12/01/2016'), 4000)
>>INSERT INTO va VALUES (3, CTOD('11/04/2016'), 3000)
>>INSERT INTO va VALUES (3, CTOD('10/04/2016'), 6000)
>>INSERT INTO va VALUES (3, CTOD('01/05/2016'), 1500)
>>INSERT INTO va VALUES (3, CTOD('08/01/2015'), 4200)
>>INSERT INTO va VALUES (3, CTOD('01/05/2015'), 3800)
>>
>>SELECT va.Id_number, va.Show_date, va.Attendance ;
>>	FROM va ;
>>	INNER JOIN (;
>>		SELECT vaspan.id_number, MIN(vaspan.show_date) AS least_recent, MAX(vaspan.show_date) AS most_recent ;
>>			FROM va AS vaspan ;
>>				INNER JOIN ;
>>					(SELECT id_number, MAX(show_date) AS show_date ;
>>						FROM va ;
>>						WHERE attendance > 5000 ;
>>						GROUP BY id_number) AS vax5000 ON vax5000.id_number = vaspan.Id_number AND vaspan.show_date > vax5000.show_date ;
>>			GROUP BY vaspan.id_number ;
>>			HAVING most_recent - least_recent > 90) AS vau5000 ON vau5000.id_number = va.Id_number AND vau5000.most_recent = va.Show_date  
>>

>
>Wow ok - well that is a step in the right direction because it does end up with the data I want - Thanks!

Instead of HAVING most_recent - least_recent > 90, for MSSQL it will be HAVING DATEDIFF(day, least_recent, most_recent) > 90. Except for the semicolons at the end of the line, the rest can be left untouched.