SQLTeam.com | Weblogs | Forums

Subtract Two Fields and convet to Text

sql2008r2

#1

HI Experts !
Needs to subtract from column1 to column2,column3 to column4 and convert integers to text then concatenate them for reporting purpose?


#2

It would perhaps be easier to do the conversions in the reporting service you are using. Nonetheless if you want to do that in SQL

SELECT CAST( column1-column2 AS VARCHAR(32)) + ' ,' + CAST(column3-colum4 AS VARCHAR(32))
FROM YourTable;

If any of the four columns are NULL, you will get a NULL in the output. If that is not what you want,

SELECT COALESCE(CAST( column1-column2 AS VARCHAR(32)) ,'')
  + ' ,' 
  + COALESCE(CAST(column3-colum4 AS VARCHAR(32)),'')
FROM YourTable;

#3

James this wonderful ...
The column values needs to be round to 0 ?

SELECT COALESCE(CAST( column1-column2 AS VARCHAR(32)) ,'')
  + ' ,' 
  + COALESCE(CAST(column3-colum4 AS VARCHAR(32)),'')
FROM YourTable;

#4

One of these.

-- this sets the result to zero if the result of the subtraction is null
SELECT COALESCE(CAST( column1-column2 AS VARCHAR(32)) ,'0')
  + ' ,' 
  + COALESCE(CAST(column3-colum4 AS VARCHAR(32)),'0')
FROM YourTable;

-- this sets each column to zero if it is null and then subtracts.
SELECT CAST( COALESCE(column1,0)-COALESCE(column2,0) AS VARCHAR(32)) 
  + ' ,' 
  + CAST(COALESCE(column3,0)-COALESCE(column4,0) AS VARCHAR(32))
FROM YourTable;

#5

Thanks the results shows great , little change the values needs to be round like 1145 ,925 and 0.00 values not need to show in the results.

  1. 1445 ,925

2.. 34 ,50 ,28


#6

This will remove .00 values

SELECT CAST( ceiling(COALESCE(column1,0)-COALESCE(column2,0)) AS VARCHAR(32))
+ ' ,'
+ CAST(ceiling(COALESCE(column3,0)-COALESCE(column4,0)) AS VARCHAR(32))
FROM YourTable;

#7

viggneshwar I mean to eliminate /trim where the subtraction goes to 0 ( Zero) and also round the values like already you did it.


#8

Try this, I have eliminated zeros

SELECT case when COALESCE(column1,0)-COALESCE(column2,0) = 0 then '' else
       CAST( ceiling(COALESCE(column1,0)-COALESCE(column2,0)) AS VARCHAR(32)) + ' ,' end
   + case when COALESCE(column3,0)-COALESCE(column4,0)= 0 then '' else 
      CAST(ceiling(COALESCE(column3,0)-COALESCE(column4,0)) AS VARCHAR(32)) end
FROM YourTable

#9

Thank you James and viggneshwar for your help.