Tags

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


SQL Puzzle | The Case & Between Puzzle

Write a T-SQL to use case and between in a query. In this if the Wording is like ‘%My Wording%’ then we have put a filter on datefields between dates (‘1/1/16’ AND ‘2/1/16’)

Please check out the sample input and the expected output.

Sample Inputs

ID Wording DateFields
1 My Wording 2016-01-01 00:00:00.000
2 My Wording 2016-02-01 00:00:00.000
3 Pawan 2016-01-01 00:00:00.000
4 Ramehs 2016-05-01 00:00:00.000
5 My Wording 2016-05-01 00:00:00.000

Expected Output

ID Wording DateFields
1 My Wording 2016-01-01 00:00:00.000
2 My Wording 2016-02-01 00:00:00.000
3 Pawan 2016-01-01 00:00:00.000
4 Ramehs 2016-05-01 00:00:00.000

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE Wordings
(
	 ID INT
	,Wording VARCHAR(100)
	,DateFields DATETIME
)
GO

INSERT INTO Wordings VALUES
(1,'My Wording','1/1/16'),
(2,'My Wording','2/1/16'),
(3,'Pawan','1/1/16'),
(4,'Ramehs','5/1/16'),
(5,'My Wording','5/1/16')
GO

--

SOLUTION – 1


--

SELECT * FROM Wordings
WHERE     DateFields >=  
			CASE WHEN Wording LIKE '%My Wording%' THEN '1/1/16' ELSE DateFields END
AND 
	  DateFields <=  
			CASE WHEN Wording LIKE '%My Wording%' THEN '2/1/16' ELSE DateFields END

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements