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!