T-SQL iterative calculation without loop-CodePudding

I have below financial data, I need to show balance as on date. I have used below logic using loop, but I want to achieve same thing without loop or cursor, using single query. Any idea on this will be helpful.

 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;

I have very large amount of such data, and my requirement is to avoid cursor or loop for this calculation.

CodePudding user response：

I think it's a running total, so you need a windowed SUM() and a CASE expression:

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

CodePudding user response：

Have you tried a simple query like this:

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

Basically you just count all Credits and subtract all Debits, up until the date.

Or, as suggested by @Zhorov:

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