0

The following piece of code is resulting in a value with 4 decimals.

COALESCE (SUM( CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' and PLL_REVENUETYPE = 'OTHER' then CAST(ROUND(PLL_AMOUNT,2) as decimal (9,2))
WHEN PLL_REVENUETYPE    = 'TAX' THEN CAST(ROUND(PLL_TAXAMOUNT,2) as decimal (9,2))
WHEN PLL_ISINCLUSIVETAX = '-'   THEN CAST(ROUND(PLL_NETAMOUNT,2) as decimal (9,2))
ELSE CAST(ROUND(PLL_NETAMOUNT,2)as decimal (9,2)) END ), 0) * -1.00 AS AMOUNT

I found many suggestions on the web, but I haven't been able to round the amount with 2 decimals.

Also, when I do a SQMCMD to export the query to a CSV, it will drop the leading zero if the amount is 0.00

Any help would be appreciated.

Improve this question
1
  • Regarding the leading zero, there's no way for SQL Server to return a numeric value with a leading zero unless you return that value as a string. And as far as the number of decimals, the answer by @Mordechai should be the way to go, but if you are still experiencing issues, as seems to be indicated by your comment under the answer, then it would be hard to resolve them without a clear example demonstrating the problem.
    – Andriy M
    Commented Dec 24, 2020 at 9:50

1 Answer 1

Reset to default
2

You don't need both cast and round. For example:

declare @val decimal(9,2) = '849.909839'
select @val [val]
, cast('849.909839' as decimal(9,2))*-1.00 [cast_first]
, cast(convert(float,'849.909839') *-1.00 as decimal(9,2)) [cast_last]

If you run this, you can see the problem. It's in the else clause. you should do a cast after you multiply by -1.00.

Improve this answer
3
  • Thanks @Mordechai, based on your recommendation, I've changed the piece of code to: , COALESCE (CAST(SUM(CASE WHEN PLL_TRANSACTIONTYPE = 'TAX' AND PLL_REVENUETYPE = 'OTHER' THEN PLL_AMOUNT WHEN PLL_REVENUETYPE = 'TAX' THEN PLL_TAXAMOUNT WHEN PLL_ISINCLUSIVETAX = '-' THEN PLL_NETAMOUNT ELSE PLL_NETAMOUNT END) * -1.00 AS DECIMAL(24,2)), 0) AS AMOUNT But unfortunately, the result is still 6 decimals.
    – Gertje
    Commented Dec 19, 2020 at 2:40
  • here is a simplified version of your code - DECLARE @var FLOAT = 30869862812595.45869 SELECT COALESCE(CAST(@var* -1.00 AS decimal(24,2)), 0). how many places after the decimal point does this return on your system?
    – Mordechai
    Commented Dec 22, 2020 at 15:09
  • 1
    I'm not sure what you mean by the ELSE clause, the OP's ELSE branch has the same rounding and conversion as every WHEN...THEN branch in that CASE, so the result of the CASE expression itself is always decimal(9,2). But I agree with you completely that in order for the final result to have that data type, they should do the conversion after all the calculations, and the OP does indeed have multiplication by -1.00 at the outermost level. (It's not in the ELSE clause, it's actually outside everything, including the COALESCE function.)
    – Andriy M
    Commented Dec 24, 2020 at 9:52

Not the answer you're looking for? Browse other questions tagged or ask your own question.