SQL Server How do subtract each row value from the Total?

frebpwbc 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(95)

I'm using SQL Server 2019, I have a table with rows containing amounts, and I need to adjust/deduct each row from the total while maintaining a running balance. I'm looking for a query that will give me two columns value:

Adjusted: The deducted or adjusted value from the total.
Balance: The remaining total value after adjustment.

Please note that in the example below, the total is fully adjusted for IDs 1 and 2, while for ID 3, only the remaining balance is adjusted, at the same time ID 4 no value adjusted due to zero remaining balance. What would be the most efficient query to achieve this result?

TOTAL = 1000.00

    Id  Amount    Adjusted   Balance
    -----------------------------------------------
    1    150.00    150.00    850.00
    2    250.00    250.00    600.00
    3    700.00    600.00      0.00
    4    450.00      0.00      0.00

This is the script for my table:

DECLARE @TotalAmount FLOAT=1000

DROP TABLE IF EXISTS [#MyTable];

SELECT Id, Amount, Adjusted, Balance 
INTO [#MyTable] 
FROM 
    (SELECT (1)Id,(150.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (2)Id,(250.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (3)Id,(700.00)Amount,(0.00)Adjusted,(0.00)Balance union
     SELECT (4)Id,(450.00)Amount,(0.00)Adjusted,(0.00)Balance
) [MyTable];

SELECT * FROM [#MyTable]

sql-server

来源：https://stackoverflow.com/questions/77289310/how-do-subtract-each-row-value-from-the-total

1条答案

按热度按时间

0mkxixxg1#

You can do something like this using the sum aggregate:

SELECT  ID, Amount
,   CASE WHEN @TotalAmount - runningSum >= 0 THEN Amount WHEN runningSum -Amount >= @TotalAmount THEN 0 ELSE @TotalAmount - runningSum + Amount END AS Adjusted
,   CASE WHEN runningSum > @TotalAmount THEN 0 ELSE @TotalAmount - runningSum END AS Balance
FROM    (
    SELECT  *
    ,   SUM(Amount) OVER(ORDER BY ID) AS runningSum
    FROM    [#MyTable]
    ) x

There are a couple of CASE WHENs:

CASE WHEN @TotalAmount - runningSum >= 0 THEN Amount WHEN runningSum -Amount >= @TotalAmount THEN 0 ELSE @TotalAmount - runningSum + Amount END

This takes case of three cases:

We have still balance to take after applying the current row: then we have fully applied the row
If we have overdrafted before the current row already, it means 0 in adjustment
Otherwise we apply as much as possible, but not so much as we get the overdraft

CASE WHEN runningSum > @TotalAmount THEN 0 ELSE @TotalAmount - runningSum END

This take care of two cases:

If we are over the total amount, then balance is 0
Otherwise, the balance left is total - running total

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server How do subtract each row value from the Total?

1条答案

相关问题

热门标签

最新问答