>>arrggg - this is so confusing! --- but I think I'm closer. I changed a couple things around and added some more data....
>>
>>Lets say the data looked like what I have below....
>>...so what should happen - is:
>>
>> for id_number 1:
>> it should only look at the first 3 records, because the 4th one has attendance of over 5000 - ...so it should ignore that one and the rest of the ones for that id_number below it (older ones). Since that "streak" was over 90 days, then I want to return the first record.
>>
>>for id_number 2:
>> all attendance is less than 5000 except for the last record - so throw that one out - and the resulting 'streak' is beyond 90 days, for return the first record for id_number 2.
>>
>>Note: I used the word LessThanSign because the UT is flipping out if I use the < character
>>
>>declare @t table (Id_number int, show_date date, attendance int)
>>insert into @t (Id_number, show_date, attendance)
>>values
>>(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)
>>
>>;with cte as (select *, COALESCE(lag(show_date) over (partition by Id_number order by show_date), show_date) as PreviousDate
>>from @t),
>>cte2 as (select *, datediff(day, PreviousDate, show_date) as [Days Passed],
>>row_number() over (partition by Id_number order by show_date) as Rn from cte
>>where attendance < 5000)
>>
>>select * from cte2 where [Days Passed] > 90 and Attendance LessThanSign 5000 order by id_number asc, show_date desc
>>
>>
>>so when I run this - I do get some data back of course - but it's not showing the first record for id_number = 1.
>>
>>
>>Pretty tricky problem I guess. I really appreciate your help :)
>>
>>Thanks!
>
>So, do I understand correctly that by "streak" you mean time difference passed between the earliest date and the last date before the attendance was over 5000? What if the 'streak' was less than 90 days?
>
>

>declare @t table (Id_number int, show_date date, attendance int)
>insert into @t (Id_number, show_date, attendance)
>values
>(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);
>
>;with FirstOver5000 as (select Id_number, min(show_date) as BigDate from @t
>where attendance > 5000
>group by Id_number)
>
>
>, 
>Streaks as (select t.Id_Number, Min(t.show_date) as StartDate, Max(t.show_date) as EndDate,
>datediff(day, min(t.show_date), max(t.show_date)) as Streak
>from @t t inner join FirstOver5000 f on t.Id_number = f.Id_number and t.show_date > f.BigDate
>group by t.Id_number)
>
>
>select t.*, s.*
>from @t t inner join Streaks s on t.Id_number = s.Id_number and t.show_date = s.StartDate -- change this to EndDate if you want the latest row
>where s.Streak > 90

That is very close - but it's showing the record from the last date in the streak, not the first date in the streak.

Antonio was able to get it to work in VFP this is what he came up with (assuming the table is named va)

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


so if this can be converted to MS SQL Server syntax - then I'm good --
thanks so much Naomi - this is being a very pesky riddle !