asked
How to subtract values from same table but different columns
Hello, I need to subtract columns C and B (C-B) from a table. Here is the query to create the table. CREATE TABLE My_TABLE1 ( A int, B nvarchar(10), C nvarchar(10) ) INSERT INTO My_TABLE1 VALUES (12,'--','8'), (7,'2', '.03'), (3, '.03','4'), (10, '3','5'), (18, '--','7') Here is the output. SELECT * FROM My_TABLE1 ![alt text][1] When subtract 'C - B' the fourth column will show the difference. ![alt text][2] [1]: /storage/temp/3921-result.png [2]: /storage/temp/3922-result2.png Thank you for your help.
Something like this should work: SELECT [a], [b], [c], CASE [b] WHEN '--' THEN b ELSE CAST(CAST(c AS DECIMAL(5,2)) - CAST(b AS DECIMAL(5,2)) AS NVARCHAR(5)) END AS Diff_C_B FROM dbo.My_Table1 go It's worth noting though if you are going to store numbers and do math on them it would be better to store them at numeric data types (int, decimal, numeric, etc) other wise you'll have to do a CAST/CONVERT on them with the values you have in the [B] column. Hope that helps.
18 People are following this question.
