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

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

---

想要改进此问题吗？添加详细信息并通过

闭合 2年前。

Answer 1

这应该做到！

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

Answer 2

好的。根据假定的要求在这里吐痰

客户根据特定日期购买了未付的购买，将其放置了一个选项卡，然后负数表示付款。如果将付款适用于日期订单债务，则每个日期的剩余余额是多少？

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