Using Results from CASE Statement to create a calculated Field

  • Hey all,

    Sorry for asking so many questions, but I'm usually not able to easily find solutions to the problems I face. This particular problem is as such. I have a numerical column and a string. If the string matches a condition, I want to make it negative and then add with another column.

    Let's say in my table I have column

    TransactionID, TransactionDescription, Amount, Adjustment

    In my query, I want to create a "TotalAmount" which would be Amount+adjustment and I also want to make it such, that if the TransactionDescription is "Negative" then the Amount will turn negative before evaluating TotalAmount.

    I would also like to display the Amount column after case as newamount.

    I got this, but couldn't get it to work since NewAmount is not an existing column.

    SELECT 

    CASE TransactionDescription = "Negative" THEN -Amount 

    ELSE Amount 

    END AS newAmount, newAmount+Adjust as TotalAmount

    From tbl_transactions

    Is something like this possible?

     

    • This topic was modified 4 months ago by  Imrans23.
  • Your case statement is a bit off... try this:

    use tempdb;
    go

    CREATE TABLE tibbleTransactions(TransactionID INT IDENTITY, TransactionDescription VARCHAR(10), Amount INT);
    GO
    INSERT INTO tibbleTransactions(TransactionDescription, Amount) VALUES ('Positive',10),('Negative',20),('Positive',20);

    SELECT TransactionID, TransactionDescription, Amount
    FROM tibbleTransactions;

    SELECT TransactionID, Amount, TransactionDescription,
    RepairedAmount = CASE WHEN TransactionDescription = 'Negative' THEN -1 * Amount ELSE Amount END
    FROM tibbleTransactions

    If you're trying to do a running total (seems like of what I'm calling "RepairedAmount"), then you need to use a windowing function, like this:

    SELECT t.TransactionID, 
    t.TransactionDescription,
    t.RepairedAmount,
    RunningSum = SUM(t.RepairedAmount) OVER (ORDER BY t.TransactionID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM
    (SELECT TransactionID, Amount, TransactionDescription,
    RepairedAmount = CASE WHEN TransactionDescription = 'Negative' THEN -1 * Amount ELSE Amount END
    FROM tibbleTransactions) t
  • You could also move the CASE statement into a CROSS APPLY to calculate the newAmount.

    SELECT 
    ca.newAmount
    , ca.newAmount + t.Adjust as TotalAmount
    From tbl_transactions AS t
    CROSS APPLY (SELECT CASE t.TransactionDescription = "Negative" THEN -t.Amount
    ELSE t.Amount END AS newAmount) AS ca

    • This reply was modified 4 months ago by  kherald69. Reason: Fixed missing table/field aliasing
    • This reply was modified 4 months ago by  kherald69.
  • Imrans23 wrote:

    Hey all,

    Sorry for asking so many questions, but I'm usually not able to easily find solutions to the problems I face. This particular problem is as such. I have a numerical column and a string. If the string matches a condition, I want to make it negative and then add with another column.

    Let's say in my table I have column

    TransactionID, TransactionDescription, Amount, Adjustment

    In my query, I want to create a "TotalAmount" which would be Amount+adjustment and I also want to make it such, that if the TransactionDescription is "Negative" then the Amount will turn negative before evaluating TotalAmount.

    I would also like to display the Amount column after case as newamount.

    I got this, but couldn't get it to work since NewAmount is not an existing column.

    SELECT 

    CASE TransactionDescription = "Negative" THEN -Amount 

    ELSE Amount 

    END AS newAmount, newAmount+Adjust as TotalAmount

    From tbl_transactions

    Is something like this possible?

    You really need to read the article at the first link in my signature line below for how to provide some "readily consumable" data.

    Here's another method you can use.  Because it's basically unlimited in the number of rows you can quickly create, it's great for performance testing when you don't have enough real data.  I've included two articles in the header below for how it works.  I normally use my  dbo.fnTally() function for this instead of the CROSS JOIN but one lesson at a time. 😀

    --=================================================================================================
    -- Create a table with some random but constrained test data.
    -- See the following links for how this works. This takes less than a seond on my laptop.
    -- https://www.sqlservercentral.com/articles/generating-test-data-part-1-generating-random-integers-and-floats-1
    -- https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates
    -- This has NOTHING to do with the solution. We''re just making a populated test table here.
    --=================================================================================================
    --===== Make reruns in SSMS easier.
    DROP TABLE IF EXISTS #MyHead;
    GO
    --===== Create and populate the test table on-the-fly with a million rows of data.
    SELECT TOP (1000000)
    TransactionID = IDENTITY(INT,1,1)
    ,TransactionDescription = IIF(ABS(CHECKSUM(NEWID())%2) = 1, 'SomethingElse','Negative')
    ,Amount = CONVERT(DECIMAL(9,2),RAND(CHECKSUM(NEWID()))*90.0+10)
    ,Adjustment = CONVERT(DECIMAL(9,2),RAND(CHECKSUM(NEWID()))*10.0)
    INTO #MyHead
    FROM sys.all_columns ac1 --"Pseduo-Cursor" replaces a loop
    CROSS JOIN sys.all_columns ac2
    ;
    --===== See what the first 100 rows look like.
    SELECT TOP (100) *
    FROM #MyHead
    ORDER BY TransactionID
    ;

    And here's an easy solution.  While I appreciate the use of CROSS APPLY (and it IS supported in 2008), I think it's an unnecessary complication here.

    --=================================================================================================
    -- One solution using the technology in SQL Server 2008.
    -- p.s. 2008 is now 14 years old and it hasn''t been supported for years.
    -- Consider upgrading. It will make your server more secure and easier to do things with.
    --=================================================================================================
    SELECT *
    ,TotalAmount = CASE WHEN TransactionDescription = 'Negative' THEN -Amount ELSE Amount END
    + Adjustment
    FROM #MyHead
    ORDER BY TransactionID
    ;

    Here's what the first several rows of the results look like.  The numbers will be different when you run it because we ARE using random constrained data here.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply