>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?

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