>>>If I have a table that looks like this:
>>>
>>>Id_number show_date attendance
>>>1 12/01/12016 4000
>>>1 03/04//2016 3000
>>>1 01/05/2016 9500
>>>2 03/01/2016 2000
>>>2 02/04/2016 4000
>>>2 01/01/2016 5000
>>>2 12/18/2015 1000
>>>
>>>
>>>What I'm TRYING I'm trying to do is if:
>>>
>>>For each ID, sorted by date, look until the attendance exceeds 5000. At that point if the show_date difference between the most recent one, and the last one that didn't exceed 5000 is beyond 90 days, then return the 3 fields from the most recent one. Ugggg - I can think of a zillion ways to do it in VFP but not so sure on SQL Server.
>>>Thanks in advance :)
>>
>>Do you want to take SUM of attendance until it exceeds 5000 or just take the earliest date when you got more than 5000?
>
>Don't want the SUM of the attendance , just the earliest date when we got to more than 5000
>
>>This can get you started:
>>
>>

>>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,  '03/04/2016', 3000),
>>(1, '01/05/2016', 9500),
>>(2, '03/01/2016', 2000),
>>(2, '02/04/2016', 4000),
>>(2, '01/01/2016', 5000),
>>(2, '12/18/2015', 1000)
>>
>>;with cte as (select *, COALESCE(lag(show_date) over (partition by Id_number order by show_date), show_date) as PreviousDate,
>>SUM(Attendance) over (partition by ID_number order by show_date) as RunningTotal
>>from @t)
>>
>>select *, datediff(day, PreviousDate, show_date) as [Days Passed] from cte
>>

Do you want to find the very first date when attendance was greater than 5000 and check the difference with it or find the first record when attendance was greater than 5000 but the previous date was more than 90 days?

For the former case:

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,  '03/04/2016', 3000),
(1, '01/05/2016', 9500),
(2, '03/01/2016', 2000),
(2, '02/04/2016', 4000),
(2, '01/01/2016', 5000),
(2, '12/18/2015', 1000)

;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 Rn = 1 and [Days Passed] > 90

For the latter case it will be

select top (1)* from cte where [Days Passed] > 90 and Attendance > 5000 order by show_date

-- no need for cte2