T-SQL Query | [ The Two Decimal Puzzle ] – In this puzzle we have to round the values up to decimal places and if values after decimal is zero then don’t show zero values. E.g for 100.119 rounded value will be 100.12 and for 100.001 the rounded value should be 100 . Please check out the sample input and expected output for details

Sample Input

 Val 100.119 1.119 111.119 81.115 90.114 100.110 100.000 100.001 100.019

Expected Output

 Val 100.12 1.12 111.12 81.12 90.11 100.11 100 100 100.02

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create Table CREATE TABLE [dbo].[TwoDecimal] ( [Val] Money ) GO --Insert Data INSERT INTO [TwoDecimal] VALUES (100.119), (1.119), (111.119), (81.115), (90.114), (100.110), (100.000), (100.001), (100.019) --Verify Data SELECT * FROM [TwoDecimal] ```

Solution 1

 ``` -- /* ** Solution 1 */ SELECT [Val], CAST(CAST(Val AS DECIMAL(6,2)) AS FLOAT) [FormattedValue] FROM TwoDecimal -- ```

Solution 2

 ``` -- /* ** Solution 2 */ SELECT [Val], CAST(FORMAT(Val , 'N2') AS FLOAT) [FormattedValue] FROM TwoDecimal -- ```

Solution 3

 ``` -- /* ** Solution 3 */ SELECT [Val], CAST(ROUND(val,2) AS FLOAT) [FormattedValue] FROM TwoDecimal -- ```

Solution 4

 ``` -- /* ** Solution 4 */ SELECT [Val], CONVERT(DOUBLE PRECISION, ROUND(val,2)) [FormattedValue] FROM TwoDecimal -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com