根据日期从同一列中减去值，若结果为负数，则继续使用

Question

DATE	VALUE
2019-04-12	2178
2019-05-31	2178
22020-06-30	15244,5
2021-09-20	16498,34
2022-02-02	-16367,4

应该这样做！

DROP TABLE YourTable
CREATE TABLE YourTable
(
Date DATE,
Value DECIMAL(10, 2)
)
GO
INSERT INTO YourTable
VALUES
('2019-04-12', '2178'),
('2019-05-31', '2178'),
('2020-06-30', '15244.5'),
('2021-09-20', '16498.34'),
('2022-02-02', '-16367.4')
ALTER TABLE YourTable
ADD RowNumber INT NULL
GO
ALTER TABLE YourTable
ADD CalculatedValue DECIMAL(10, 2) NULL
GO
UPDATE A
SET A.RowNumber = A.Ranks
FROM
(
SELECT RowNumber, ROW_NUMBER() OVER (ORDER BY DATE ASC) AS Ranks
FROM YourTable
WHERE Value > 0
) A
UPDATE YourTable
SET CalculatedValue =
(
SELECT SUM(Value)
FROM YourTable
WHERE Value < 0
) + Value
WHERE RowNumber = 1
DECLARE @i INT = 1
DECLARE @iMax INT =
(
SELECT MAX(RowNumber)
FROM YourTable
)
WHILE @i <= @iMax
BEGIN
UPDATE A
SET A.CalculatedValue = B.CalculatedValue + A.Value
FROM YourTable A
JOIN YourTable B ON
A.RowNumber = @i
AND B.RowNumber = @i - 1
SET @i = @i + 1
END
UPDATE YourTable
SET CalculatedValue = Value
WHERE RowNumber > 
(
SELECT MIN(RowNumber)
FROM YourTable
WHERE CalculatedValue > 0
)
SELECT Date, CalculatedValue AS 'Value'
FROM YourTable
WHERE CalculatedValue >= 0
ORDER BY RowNumber

好。此处的Spitballing基于假定的需求

客户根据特定日期的未付款购买情况累积了一个账单，那么负数表示付款。如果按日期顺序对债务进行付款，那么每个日期的剩余余额是多少？

create table t (
dt date,
val decimal(8,3)
)
insert t
values 
({d '2019-04-12'}, 2178)
, ({d '2019-05-31'}, 2178)
, ({d '2020-06-30'}, 15244.5)
, ({d '2021-09-20'}, 16498.34)
, ({d '2022-02-02'}, -16367.4)
;
with a as (
select dt
, val
, SUM(val) OVER (ORDER BY case when val < 0 then 1 else 2 end, dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as oldbal
from t
),
b as (
select dt
, val + case when oldbal < 0 then oldbal else 0 end as RemainingBalance
from a
)
select dt as 'DATE'
, RemainingBalance as 'VALUE'
from b
where RemainingBalance > 0
order by dt
;
--  Correct values for comparison
select cast([DATE] as date) as 'DATE'
, VALUE
from (
values 
({d '2020-06-30'}, 3233.1)
, ({d '2021-09-20'}, 16498.34)
) q ([DATE], [VALUE])

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4de0258c6e670d6d4d7e8b4f717eb82a