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_transactionsIs something like this possible?
- This topic was modified 2 years, 10 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 tibbleTransactionsIf 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 - 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_transactionsIs 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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply