Subtract Two Fields and convet to Text

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

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;
1 Like

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;

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;
1 Like

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

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;

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

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
1 Like

Thank you James and viggneshwar for your help.