时间戳

首页 > 解决方案 > 如何查找超过 5 年期间超过设定金额的记录

sql - 如何查找超过 5 年期间超过设定金额的记录

问题描述

我正在尝试在自 2011 年 1 月 1 日以来的任何 5 年期间内提供超过 5000 的财务表中查找 contact_numbers。我在兜圈子。

这就是我所拥有的，但它从 2011 年开始计算一切。任何帮助都会很棒！

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

标签： sqlsql-server

解决方案

我认为你希望这个结构为：

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;

推荐阅读