SQL脚本开发需求：按客户处理交易数据，过滤非正值并调整列值至总存款限额内

Let's start by clarifying the core requirements we're addressing here:

• Process each row's transaction columns (IBFT, P2P, UBP, MAOTC, WITHDRAWAL, AIRTIME) in order
• Keep values as-is until their cumulative sum exceeds the TOTAL DEPOSIT value
• For the first column where adding its value would push the sum over the limit, cap it to the remaining available deposit (so total sum equals TOTAL DEPOSIT)
• Set all subsequent columns to 0 (since no deposit remains)
• Ignore columns with values ≤ 0 (they don't contribute to the sum)

Your nested CASE statement approach works, but it's verbose and hard to maintain if you ever need to add/remove transaction columns. Let's use a more structured, scalable method with CTEs and window functions:

Solution Code #

DROP TABLE IF EXISTS #input;
CREATE TABLE #input (
    [CONTACT NO] VARCHAR(15),
    [TOTAL DEPOSIT] BIGINT,
    IBFT BIGINT,
    P2P BIGINT,
    UBP BIGINT,
    MAOTC BIGINT,
    WITHDRAWAL BIGINT,
    AIRTIME BIGINT
);
GO

INSERT INTO #input VALUES
('92342xxxxx63',1540,0,1540,0,0,0,0),
('92343xxxxx47',42334,55000,17710,0,0,0,1045),
('92343xxxxx05',5750,53450,5000,0,0,0,2050),
('92347xxxxx53',2000,0,2800,0,0,2000,570),
('92348xxxxx62',8250,0,0,0,8000,14900,45),
('92348xxxxx66',40,0,0,0,0,0,40),
('92303xxxxx76',400,0,1507,0,0,0,300),
('92300xxxxx90',200,0,0,0,0,0,80),
('92321xxxxx93',14040,11940,101180,30198,0,0,3268),
('92333xxxxx91',45000,50000,0,0,0,0,0);
GO

WITH TransactionColumns AS (
    SELECT 
        [CONTACT NO],
        [TOTAL DEPOSIT],
        CAST(COLUMN_NAME AS VARCHAR(20)) AS ColName,
        CAST(CASE 
                WHEN COLUMN_NAME = 'IBFT' THEN IBFT
                WHEN COLUMN_NAME = 'P2P' THEN P2P
                WHEN COLUMN_NAME = 'UBP' THEN UBP
                WHEN COLUMN_NAME = 'MAOTC' THEN MAOTC
                WHEN COLUMN_NAME = 'WITHDRAWAL' THEN WITHDRAWAL
                WHEN COLUMN_NAME = 'AIRTIME' THEN AIRTIME
             END AS BIGINT) AS ColValue
    FROM #input
    CROSS APPLY (VALUES 
        ('IBFT'), ('P2P'), ('UBP'), ('MAOTC'), ('WITHDRAWAL'), ('AIRTIME')
    ) AS cols(COLUMN_NAME)
),
CumulativeSums AS (
    SELECT 
        [CONTACT NO],
        [TOTAL DEPOSIT],
        ColName,
        ColValue,
        -- Cumulative sum of positive values up to current column
        SUM(CASE WHEN ColValue > 0 THEN ColValue ELSE 0 END) 
            OVER (PARTITION BY [CONTACT NO] ORDER BY 
                CASE ColName 
                    WHEN 'IBFT' THEN 1 
                    WHEN 'P2P' THEN 2 
                    WHEN 'UBP' THEN 3 
                    WHEN 'MAOTC' THEN 4 
                    WHEN 'WITHDRAWAL' THEN 5 
                    WHEN 'AIRTIME' THEN 6 
                END 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumSum,
        -- Cumulative sum before current column
        SUM(CASE WHEN ColValue > 0 THEN ColValue ELSE 0 END) 
            OVER (PARTITION BY [CONTACT NO] ORDER BY 
                CASE ColName 
                    WHEN 'IBFT' THEN 1 
                    WHEN 'P2P' THEN 2 
                    WHEN 'UBP' THEN 3 
                    WHEN 'MAOTC' THEN 4 
                    WHEN 'WITHDRAWAL' THEN 5 
                    WHEN 'AIRTIME' THEN 6 
                END 
                ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS PrevCumSum
    FROM TransactionColumns
),
AdjustedValues AS (
    SELECT 
        [CONTACT NO],
        [TOTAL DEPOSIT],
        ColName,
        CASE 
            -- No remaining deposit left before this column
            WHEN ISNULL(PrevCumSum, 0) >= [TOTAL DEPOSIT] THEN 0
            -- Current value would exceed limit: cap to remaining deposit
            WHEN ISNULL(PrevCumSum, 0) + ColValue > [TOTAL DEPOSIT] THEN [TOTAL DEPOSIT] - ISNULL(PrevCumSum, 0)
            -- Keep original positive value (or 0 if value was <=0)
            ELSE CASE WHEN ColValue > 0 THEN ColValue ELSE 0 END
        END AS AdjustedColValue
    FROM CumulativeSums
)
-- Pivot back to original column structure
SELECT 
    [CONTACT NO],
    [TOTAL DEPOSIT],
    MAX(CASE WHEN ColName = 'IBFT' THEN AdjustedColValue END) AS IBFT_Adjusted,
    MAX(CASE WHEN ColName = 'P2P' THEN AdjustedColValue END) AS P2P_Adjusted,
    MAX(CASE WHEN ColName = 'UBP' THEN AdjustedColValue END) AS UBP_Adjusted,
    MAX(CASE WHEN ColName = 'MAOTC' THEN AdjustedColValue END) AS MAOTC_Adjusted,
    MAX(CASE WHEN ColName = 'WITHDRAWAL' THEN AdjustedColValue END) AS WITHDRAWAL_Adjusted,
    MAX(CASE WHEN ColName = 'AIRTIME' THEN AdjustedColValue END) AS AIRTIME_Adjusted,
    [TOTAL DEPOSIT] - SUM(AdjustedColValue) AS [REMAINING DEPOSIT]
FROM AdjustedValues
GROUP BY [CONTACT NO], [TOTAL DEPOSIT]
ORDER BY [CONTACT NO];

How It Works #

1. Unpivot Transaction Columns: Convert the fixed transaction columns into a row-based format using CROSS APPLY—this makes sequential processing and cumulative sum calculations much easier.
2. Calculate Cumulative Sums: Use window functions to compute the running total of positive values up to each column, plus the total before the current column. This tells us exactly when we hit the deposit limit.
3. Adjust Values: For each column, we:
   • Set to 0 if there's no remaining deposit left before processing it
   • Cap the value to the remaining deposit if adding it would exceed the limit
   • Keep the original positive value otherwise
4. Pivot Back: Convert the row-based adjusted values back to the original column structure, then calculate the remaining deposit for each row.

This approach is far more maintainable—if you need to add/remove transaction columns, you only need to update the VALUES clause in the unpivot step and the ordering logic in the window function.

内容的提问来源于stack exchange，提问作者Anwaar E Mustafa