T-SQL迭代计算无循环

Question

我有以下财务数据，我需要显示截至日期的余额。我已经使用下面的逻辑使用循环，但我想实现同样的事情没有循环或游标，使用单个查询。关于这个问题，有什么意见会很有帮助的。

DROP TABLE IF EXISTS #Transact;
CREATE TABLE #Transact
(
dt DATE,
Transact VARCHAR(1),
Amount INT,
Balance INT
)

INSERT INTO #Transact
(
dt,
Transact,
Amount
)
SELECT *
FROM
(
VALUES
('01-01-2022', 'C', 1000),
('02-01-2022', 'C', 1000),
('03-01-2022', 'D', 1000),
('04-01-2022', 'C', 2000),
('05-01-2022', 'C', 2000),
('06-01-2022', 'D', 2000),
('07-01-2022', 'D', 2000),
('08-01-2022', 'C', 3000),
('09-01-2022', 'C', 1500),
('10-01-2022', 'D', 1500),
('11-01-2022', 'D', 1500),
('12-01-2022', 'C', 1500)
) VTE (DT, Transact, Amount);

declare @interval date = '01-01-2022',
@balance int

WHILE (@interval <= (select max(dt) FROM #Transact))
BEGIN
;WITH CTE
AS (SELECT *,
LAG(Balance) Over (Order by dt) AS PreBalance
FROM #Transact
)
UPDATE CTE
SET Balance = CASE
WHEN Transact = 'C' THEN
Amount + ISNULL(PreBalance, 0)
ELSE
ISNULL(PreBalance, 0) - Amount
END
WHERE dt = @interval

SET @interval = DATEADD(DAY, 1, @interval)
SELECT @balance = Balance
from #Transact
where dt = @interval
END

SELECT * FROM #Transact;

我有很多这样的数据，我的要求是在这个计算中避免游标或循环。

Answer 1

我认为这是一个运行总数，所以您需要一个窗口SUM()和CASE表达式:

SELECT 
dt, Transact, Amount, 
SUM(
CASE 
WHEN Transact = 'C' THEN Amount 
WHEN Transact = 'D' THEN -Amount
ELSE 0
END) OVER (ORDER BY dt) AS Balance
FROM #Transact   

Answer 2

您是否尝试过这样一个简单的查询:

SELECT
(
SELECT SUM(Amount) FROM #Transact WHERE Transact = 'C' and dt <= @interval
)
-
(
SELECT SUM(Amount) FROM #Transact WHERE Transact = 'D' and dt <= @interval
)
as Balance

基本上你只需要计算所有的贷方并减去所有的借方，直到日期。

或者，正如@Zhorov所建议的:

SELECT SUM
(
CASE 
WHEN Transact = 'C' THEN Amount 
WHEN Transact = 'D' THEN -Amount
ELSE 0
END
) as Balance
FROM #Transact WHERE dt <= @interval