Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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