How to find records with over a set amount over a 5 year period

I’m trying to find contact_numbers in a financial table who have given over 5000 in any 5 year period since 01/01/2011. I’m going around in circles.

This is what I have but it’s calculating everything from 2011. Any help would be great!

select t1.contact_number,
sum(Amount) as amount
from batch_transactions t1 
inner join contacts c on c.contact_number = t1.contact_number
cross apply (
    select sum(amount) Rolling_sum 
    from batch_transactions 
    where contact_number = t1.contact_number 
      and datediff(year, transaction_date, t1.transaction_date) <= 5
     and transaction_date >= '2011-01-01' and contact_type <> 'O'
      and t1.transaction_date >= transaction_date
) o where t1.transaction_date >= '2011-01-01' and contact_type <> 'O' 
 and datediff(year, transaction_date, t1.transaction_date) <= 5
group by t1.contact_number
having sum(amount) > 5000
order by contact_number

Answer

I think you want this structured as:

select c.contact_number, bt2.rolling_sum
from contacts c join
     batch_transctions bt
     on cross apply
     (select sum(bt2.amount) as Rolling_sum 
      from batch_transactions bt2
      where bt2.contact_number = c.contact_number and
            bt2.transaction_date >= bt.transaction_date and
            bt2.transaction_date < dateadd(year, 5, bt.transaction_date)
     ) bt2
where bt2.rolling_sum > 5000;