CODEWITHSUNDEEP
sqlsql-server
Sophairk
Sophairk

Reputation: 273

Set variable in select statement?

Raw data:

enter image description here

This what I want:

enter image description here

Here is my query:

DECLARE @BAL DECIMAL(38, 2) = 1000

SELECT
    ID
    ,CASE WHEN [TYPE] = 'CREDIT' THEN AMT ELSE 0 END CREDIT_AMT
    ,CASE WHEN [TYPE] = 'DEBIT' THEN AMT ELSE 0 END DEBIT_AMT
    ,CASE WHEN [TYPE] = 'CREDIT' THEN AMT + @BAL ELSE AMT - @BAL END BAL
FROM
    @TB

My problem I cannot SET @BAL += AMT or @BAL -= AMT inside the SELECT statement.

Upvotes: 1

Views: 95

Answers (1)

Max Zolotenko
Max Zolotenko

Reputation: 1132

If your SQL Server is 2012+, you can try this:

DECLARE @BAL DECIMAL(38, 2) = 1000
DECLARE @TB TABLE (ID INT, TYPE varchar(100), AMT money);

INSERT  @TB
    VALUES  (1, 'CREDIT', 10),
            (2, 'DEBIT', 15),
            (3, 'DEBIT', 5),
            (4, 'CREDIT', 20),
            (5, 'DEBIT', 10)

SELECT  ID,
        CASE WHEN [TYPE] = 'CREDIT' THEN AMT ELSE 0 END CREDIT_AMT,
        CASE WHEN [TYPE] = 'DEBIT' THEN AMT ELSE 0 END DEBIT_AMT,
        SUM(CASE WHEN [TYPE] = 'CREDIT' THEN AMT ELSE -AMT END) OVER(ORDER BY ID)+@BAL as BAL
FROM    @TB
ORDER BY ID

Upvotes: 3

Related Questions