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.
- 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.