Casting the number of decimals in a select dynamically (For eg, amounts based on the number of decimals of the currency)

Question

Post reply

Casting the number of decimals in a select dynamically (For eg, amounts based on the number of decimals of the currency)

narensantanam

SSC Journeyman

Points: 99
More actions
December 31, 2009 at 10:28 am

#136332

Hi,
As a part of our reporting stack at a bank, we expose off data to reporting tools in the form of views etc. Being a bank, most reports contain amounts that are in different currencies. A requirement arose to be able to format the number of decimals at a view level while pulling through an amount column (such as balance).
Code
DECLARE @input decimal(17,3)
DECLARE @decs tinyint
SELECT @input=1234.123,@decs=2
SELECT
CASE WHEN @decs = 2 THEN CAST(CAST(@input as decimal(17,2)) AS VARCHAR(19))
WHEN @decs = 1 THEN CAST(CAST(@input as decimal(17,1)) AS VARCHAR(19))
ELSE @input
END
SELECT CAST(@input AS decimal(17,2))
Result
SELECT 1 : 1234.120 SELECT2: 1234.12
I thought that it might be as easy as writing a CASE based cast statement, but I was wrong. The following code exhibits the behaviour of a CAST within a CASE and outside it. If you notice, that although the 1st SELECT is CASTING the number to 2 decimals, it returns a result of 3 decimals. The second SELECT returns what we expect it to return.
Can someone share some experiences that you might have had or solutions that you have applied in the past ?
I did manage to workaround by casting as VARCHAR and manipulating it but thought that there should be a better and more elegant way of doing this. Hence the post.
Cheers
Naren
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

David.Poole SSC Guru Points: 76000 More actions · Answer 1

When I've used DBMail to send reports I've formatted numbers as VARCHAR just to get the email format looking nice.

The problem comes when someone wants to use the views for something other than display. They now have something that looks like a number and should actually be a number but is actually a piece of text!

They can neither calculate or sort by that value should they wish to do so.

In general I wouldn't put formatting logic into the DB layer, I would expect the reporting tool to be able to do that for me.

LinkedIn Profile

narensantanam SSC Journeyman Points: 99 More actions · Answer 2

Hi David,

I agree with you abt the presentation layer doing this formatting. However, not all tools have the capability of dynamically formatting amounts based on a parameter value. I was just trying my luck to see if there's any other alternative 🙂

Cheers

Naren

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

DECLARE @input decimal(17,3)

DECLARE @decs tinyint

SELECT @input=1234.123,@decs=2

SELECT

CASE WHEN @decs = 2 THEN CAST(CAST(@input as decimal(17,2)) AS VARCHAR(19))

WHEN @decs = 1 THEN CAST(CAST(@input as decimal(17,1)) AS VARCHAR(19))

ELSE CAST(@input AS VARCHAR(19))

END

SELECT CAST(@input AS decimal(17,2))

This does what you wanted, although the numbers are now VARCHAR(19) instead of DECIMAL...

The thing that is happening is that the CASE statement is taking on the data type DECIMAL(17,3) from the variable @input and casting the VARCHAR(19) values back to DECIMAL(17,3) from VARCHAR(19)

CASTing the @input value to VARCHAR(19) within the CASE prevents this and you get 2 digits / 1 digit as required.

There is not really another way to do this in one column as it has to have a type that will suit all possible outcomes.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

narensantanam (12/31/2009)
Hi,
As a part of our reporting stack at a bank, we expose off data to reporting tools in the form of views etc. Being a bank, most reports contain amounts that are in different currencies. A requirement arose to be able to format the number of decimals at a view level while pulling through an amount column (such as balance).
Code
DECLARE @input decimal(17,3)
DECLARE @decs tinyint
SELECT @input=1234.123,@decs=2
SELECT
CASE WHEN @decs = 2 THEN CAST(CAST(@input as decimal(17,2)) AS VARCHAR(19))
WHEN @decs = 1 THEN CAST(CAST(@input as decimal(17,1)) AS VARCHAR(19))
ELSE @input
END
SELECT CAST(@input AS decimal(17,2))
Result
SELECT 1 : 1234.120 SELECT2: 1234.12
I thought that it might be as easy as writing a CASE based cast statement, but I was wrong. The following code exhibits the behaviour of a CAST within a CASE and outside it. If you notice, that although the 1st SELECT is CASTING the number to 2 decimals, it returns a result of 3 decimals. The second SELECT returns what we expect it to return.
Can someone share some experiences that you might have had or solutions that you have applied in the past ?
I did manage to workaround by casting as VARCHAR and manipulating it but thought that there should be a better and more elegant way of doing this. Hence the post.
Cheers
Naren

My question would be... why are you doing presentation layer stuff in the data layer? It would be best if this were done in the GUI.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

narensantanam SSC Journeyman Points: 99 More actions · Answer 5

Hi Jeff,

Like I mentioned in my reply to David - The reporting tools that we are using are unable to do this. I was just trying to find a workaround within T-SQL for this.

Thanks for your reply.

Cheers

Naren

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

narensantanam (12/31/2009)
Hi Jeff,
Like I mentioned in my reply to David - The reporting tools that we are using are unable to do this. I was just trying to find a workaround within T-SQL for this.
Thanks for your reply.
Cheers
Naren

Then, make your life simple... might as well right-justify while you're at it. Borrowing on magoo's code... notice the absence of case statements.

DECLARE @Input DECIMAL(17,3),

@Decs TINYINT

SELECT @Input=1234.123,

@Decs=1

SELECT STR(@Input,12,@Decs)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

narensantanam SSC Journeyman Points: 99 More actions · Answer 7

Hi Jeff,

Thanks a ton - this has indeed made my life a lot simpler! A very helpful post.

Thanks

Naren.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Thanks Jeff, yet another built in function I have never noticed!

The problem with all of this of course is that you are leaving yourself in the hands of SQL and it's rounding policy.

As this is for a bank, I would just make sure that you test this with a complete range of decimals to make sure you are happy with the output.

e.g. STR(4.555,12,2) gives [ 4.55] (some may think 4.56 would be a better result)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

narensantanam SSC Journeyman Points: 99 More actions · Answer 9

Hi,

Thanks for your observation on the truncation bit. I believe it should not be a problem as an account with currency as USD should never have a value in the 3rd decimal place. It could be of a concern if the amount in consideration is cross currency.

Cheers and Happy new year to all!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

mister.magoo (1/1/2010)
Thanks Jeff, yet another built in function I have never noticed!
The problem with all of this of course is that you are leaving yourself in the hands of SQL and it's rounding policy.
As this is for a bank, I would just make sure that you test this with a complete range of decimals to make sure you are happy with the output.
e.g. STR(4.555,12,2) gives [ 4.55] (some may think 4.56 would be a better result)

Yep... agreed and that would be true with the conversion from decimal to varchar, as well (not the actual rounding but the implication). And, hopefully, people understand this type of formatting should only be done for display purposes and formatted answers should never be stored as "real" data.

Heh... anyone remember the two "food fight" threads on "Banker's Rounding"? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

narensantanam (1/1/2010)
Hi,
Thanks for your observation on the truncation bit. I believe it should not be a problem as an account with currency as USD should never have a value in the 3rd decimal place. It could be of a concern if the amount in consideration is cross currency.
Cheers and Happy new year to all!

If you believe that, then I probably shouldn't have shown you STR(). Think about what happens when you multiply a dollar amount by an interest rate... LOTS and LOTS of decimal places. And think about why MS created the 4 decimal place MONEY datatype.

Just to be sure... all calculations and stored values concerning money should always have enough decimal places to be deadly accurate. The only time a rounding policy should come into play is for DISPLAY purposes and those display values probably shouldn't be stored for use.

I whole heartedly agree with magoo... Make sure that you check with someone at the bank on what their rounding policies for display and storage purposes are before you deploy any such code that may change the number of decimal places.

As a side bar... this is why it's better to do this type of formatting in some GUI or reporting service.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Heh... I'm still concerned because this is for a bank and I apologize for even suggesting the use of STR(). Magoo... I owe you an apology because I forgot that STR() will do incorrect rounding because of its FLOAT backbone. :blush:

One way to get around the mess I've made of this is to actually do rounding and then format it (see the 3rd formula below)...

DECLARE @SomeValue AS DECIMAL(12,3),

@DecPlaces AS INT

SELECT @SomeValue = 4.555,

@DecPlaces = 2

SELECT STR(@SomeValue,12,@DecPlaces),

ROUND(@SomeValue,@DecPlaces),

STR(ROUND(@SomeValue,@DecPlaces),12,@DecPlaces)

... either that or use the CASE method that magoo originally suggested.

ROUND also has a FLOAT backbone but it's designed differently so that it works correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

One more thing... rounding can be a real PITA depending on the original datatype... even the CASE method fails if the input is FLOAT because 4.555 comes out to be something like 4.554999999999998 in FLOAT behind the scenes...

DECLARE @SomeValue AS FLOAT,

@DecPlaces AS INT

SELECT @SomeValue = 4.555,

@DecPlaces = 2

SELECT STR(@SomeValue,12,@DecPlaces),

ROUND(@SomeValue,@DecPlaces),

STR(ROUND(@SomeValue,@DecPlaces),12,@DecPlaces),

CAST(CAST(@SomeValue as decimal(12,2)) AS VARCHAR(19))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 14

Jeff Moden (1/1/2010)
Heh... I'm still concerned because this is for a bank and I apologize for even suggesting the use of STR(). Magoo... I owe you an apology because I forgot that STR() will do incorrect rounding because of its FLOAT backbone. :blush:

You don't owe any apology, but thanks anyway.

I was not suggesting that your STR solution on it's own was an issue, but rounding is a real pain in any language, especially where banks are concerned, so I am sure the OP will be aware of that and have strategies in place, I just wanted to raise the point on the off-chance that the OP had not considered it as an issue.

I find a lot of people do not understand the problem with floats and assume they will translate to presentation without issue.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);