Tags

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


SQL PUZZLE | Round the Vals value to Closest 10 value | Advanced SQL

In this puzzle you have to read the column – vals and round the integer to the closest next whole number. Please see the sample input and expected output.

Sample Input

Id Vals
1 40
2 41
3 42
4 43
5 44
6 45
7 46
8 47
9 48
10 49
11 50

Expected Output

Id Vals CloseToNextTen
1 40 40
2 41 50
3 42 50
4 43 50
5 44 50
6 45 50
7 46 50
8 47 50
9 48 50
10 49 50
11 50 50

Script – DDL and INSERT Sample Data

--

CREATE TABLE CloseTen
(
	 Id INT
	,Vals INT
)
GO

INSERT INTO CloseTen VALUES
(1,40),
(2,41),
(3,42),
(4,43),
(5,44),
(6,45),
(7,46),
(8,47),
(9,48),
(10,49),
(11,50)
GO

SELECT * FROM CloseTen
GO

--

SOLUTION – 1

--

SELECT * , ((Vals + 9)/ 10) * 10 CloseToNextTen
FROM CloseTen

--

Output – 1

--

Id          Vals        CloseToNextTen
----------- ----------- --------------
1           40          40
2           41          50
3           42          50
4           43          50
5           44          50
6           45          50
7           46          50
8           47          50
9           48          50
10          49          50
11          50          50

(11 rows affected)

--

SOLUTION – 2

--

SELECT * , (((Vals - 1) / 10) + 1) * 10  CloseToNextTen
FROM CloseTen

--

Output – 2

--

Id          Vals        CloseToNextTen
----------- ----------- --------------
1           40          40
2           41          50
3           42          50
4           43          50
5           44          50
6           45          50
7           46          50
8           47          50
9           48          50
10          49          50
11          50          50

(11 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com