SQL percentage format issue

Transact-SQL
1

I have written a SQL code which has no syntax errors but it returns an empty sheet (below).

Works fine without lines 13 to 19 where I transfer the Saving into a % and turning negative values to 0.

Hope someone could advise.

Select Distinct rptviewGovtTransparencyCode.project_title As Title,
  rptviewGovtTransparencyCode.department As Department,
  rptviewContract.AwardedDateTime As Award,
  rptviewGovtTransparencyCode.estimated_value As Budget,
  Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', '')) As Value,
  Case
    When rptviewContract.estimated_value -
    Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', '')) <=
    0 Then 0
    Else rptviewContract.estimated_value -
    Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', ''))
  End As Saving,
  Case
    When (Format(((rptviewGovtTransparencyCode.estimated_value -
    Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', ''))) /
    rptviewGovtTransparencyCode.estimated_value), 'p')) <= 0 Then 0
    Else (Format(((rptviewGovtTransparencyCode.estimated_value -
    Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', ''))) /
    rptviewGovtTransparencyCode.estimated_value), 'p')) End As [%]
From rptviewGovtTransparencyCode
  Inner Join rptviewContract On rptviewContract.contract_id =
    rptviewGovtTransparencyCode.contract_id
  Inner Join rptviewCustomField On rptviewCustomField.OrgId =
    rptviewContract.OrgId
  Inner Join rptviewCustomFieldAnswer On rptviewCustomFieldAnswer.TargetAreaId =
    rptviewContract.project_id And rptviewCustomField.Id =
    rptviewCustomFieldAnswer.CustomFieldId
  Inner Join rptviewContractPrimaryContact On rptviewContract.contract_id =
    rptviewContractPrimaryContact.ContractId
Where rptviewContract.AwardedDateTime >= '2018-04-01' And
  rptviewCustomField.Title = 'awarded value'
Order By Title

Previously, lines 13 to 19 of the above were represented as

Format(((rptviewGovtTransparencyCode.estimated_value -
  Convert(decimal,Replace(rptviewCustomFieldAnswer.Answer, ',', ''))) /
  rptviewGovtTransparencyCode.estimated_value), 'p') As [%]

But it still was not clear as how to replace negative % values with 0s.

2

hi

here @a is your column which is less than zero

SELECT IIF(@a > 0, @a, 0);