I have created a SQL statement to calculate the difference between two numerical fields. Sometimes the field values are null, so I coalesce the values to zero. If the calculated difference is zero, the output should be a blank and not a zero.
Here is the statement:
convert(varchar,case when coalesce(Debit,0)-coalesce(Credit,0) > 0 then coalesce(Debit,0)-coalesce(Credit,0) else '' end ) as DR
,case when coalesce(Debit,0)-coalesce(Credit,0) < 0 then (coalesce(Credit,0)-coalesce(Debit,0)) else '' end as CR
Here is an example of the output:
AccountID AccountDescription DR CR
01.6205.2010 PTM MEDICAL/SURGICAL UNI OP MEDICARE 0.00 4180.50
How can I make the DR value ' '?