Tags

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


T-SQL Query | [ The Case Replace Puzzle ] – In this puzzle we have to write the query without “Case” statement. You can use IIF and Choose statement or any other more suitable statement to get the output. Please check out the sample input and expected output for details.

Sample Input

Vals
125
350
270
140

Expected Output ( Other than Case statement )

ValCase
100
300
200
100

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].[RangeValues]
(
[Vals] [int] NULL
)
GO

--Insert Data
INSERT INTO [RangeValues](Vals)
VALUES
(125),
(350),
(270),
(140)

--Verify Data
SELECT Vals FROM [RangeValues]

--Sample Query with Case Statement

SELECT Vals
,CASE Vals
WHEN 125 then 100
WHEN 350 then 300
WHEN 270 then 200
WHEN 140 then 100
END ValCase
FROM [dbo].[RangeValues]

UPDATE – 24-Apr-2015 – Solution 1 & 2 


--

--SOLUTION 1 | USING IF
SELECT Vals 
,IIF ( Vals = 125 , '100' , IIF (Vals = 350 , '300' , IIF( Vals = 270 , '200', IIF( Vals = 140 , '100', Vals )))) as ValCase
FROM [dbo].[RangeValues]


--SOLUTION 2 | USING CHOOSE
SELECT Vals 
,CHOOSE ( Vals / 100, '100', '200' , '300' , 'N' ) ValCase
FROM [dbo].[RangeValues]

--

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

Keep Learning

http://MSBISkills.com