sql - 如何使用 Sql 获取两个日期之间的最小金额？

Question

我需要一些逻辑来获得两个日期之间的最少金额，我的表是这样的

create table #tmp(id int,deposit_id int,transaction_amount decimal(15,2),trn_date date,trn_type varchar(50));
insert into #tmp
select 1,101,15000,'2017-12-01','Receipt' -- 15000
union all
select 2,101,-5000,'2017-12-09','Payment' -- 10000
union all
select 3,101,6000,'2017-12-16','Receipt'   --16000
union all
select 4,101,-15000,'2017-12-20','Payment' -- 1000
union all
select 5,101,10000,'2017-12-22','Receipt' --11000
union all
select 5,101,-1000,'2017-12-22','Payment' --10000
union all
--- another deposit details
select 55,102,50000,'2017-12-04','Receipt' -- 50000
union all
select 56,102,5000,'2017-12-10','Receipt' -- 55000

我想要“2017-12-01”和“2017-12-31”之间每笔存款的最少余额。我无法获得查询逻辑以获得此结果，因为我如何检查每天的余额，例如'2017-12-24'。请给点提示。我想要的结果是

deposit_id   date   LeastAmt
    101  - '2017-12-20'  - 1000
    102  - '2017-12-04'  - 50000

Answer 1

这个问题可以分为两部分。首先，我们需要计算存款表的滚动余额。然后，我们需要找到给定日期范围内每个帐户的最小余额。

WITH cte AS (
    SELECT d1.*,
        (SELECT SUM(d2.transaction_amount) FROM deposits d2
         WHERE d1.deposit_id  = d2.deposit_id AND d2.trn_date <= d1.trn_date) bal
    FROM deposits d1
)

SELECT
    id, deposit_id, transaction_amount, bal, trn_date, trn_type
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY deposit_id ORDER BY bal) rn
    FROM cte
    WHERE trn_date BETWEEN '2017-12-01' AND '2017-12-31'
) t
WHERE t.rn = 1;