Tags

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


SQL Puzzle | The Make Zero Puzzle

Puzzle Statement

This is one of the scenarios that I got while working on a SQL Script, It is very interesting bit weird also. Here you have to accept an input parameter. The parameter will have comma separated services.

1. The query should return all services present in the table that matches with services we are passing as the input parameter.

2. If the input parameter value is ‘Water,Aqua’ or ‘Water’ or ‘Aqua’ then we have to sum up cost and return the results.

3. If the input parameter value is different from above case then we have return 0 as cost and return the results.

4. Please check out the input table & some sample input strings with their expected output below-

Sample Input

ID Cost Services
1 200 Water
2 120 Water
3 240 Electricity
4 250 Electricity
5 200 Electricity
6 250 Aqua
7 230 Aqua
8 290 Aqua
9 120 Aqua
10 320 Carbon

Expected Output

Output for ‘Water,Aqua,Electricity’

Cost Services
0 Aqua
0 Electricity
0 Water

Output for ‘Water,Aqua’

Cost Services
890 Aqua
320 Water

Output for ‘Water’

Cost Services
320 Water

Rules/Restrictions – The solution should be should use “SELECT” statement or “CTE”.

Script – Use below script to create table and insert sample data into it.


--



CREATE TABLE TestCost
(
	 ID SMALLINT
	,Cost INT
	,Services VARCHAR(100) 	
)
GO

INSERT INTO TestCost
VALUES
(1,200,'Water'),
(2,120,'Water'),
(3,240,'Electricity'),
(4,250,'Electricity'),
(5,200,'Electricity'),
(6,250,'Aqua'),
(7,230,'Aqua'),
(8,290,'Aqua'),
(9,120,'Aqua'),
(10,320,'Carbon')
GO





--

SOLUTION 1

--


DECLARE @Input AS VARCHAR(100) = 'Water,Aqua,Water,Electricity'
DECLARE @DistinctCount AS SMALLINT = 0
DECLARE @ExtraCount AS SMALLINT = 0

DECLARE @x AS XML=''
DECLARE @Param AS VARCHAR(100) = @Input
SET @x = CAST('<A>'+ REPLACE(@Param,',','</A><A>')+ '</A>' AS XML)

SELECT @DistinctCount = COUNT(DISTINCT(t.value('.', 'VARCHAR(100)'))) FROM @x.nodes('/A') AS x(t)

SELECT @ExtraCount = COUNT(*) FROM (
SELECT 'Water' st
UNION ALL 
SELECT 'Aqua' ) o
FULL JOIN 
(
      SELECT t.value('.', 'VARCHAR(100)') Value FROM @x.nodes('/A') AS x(t)
)t on t.Value = o.st
WHERE o.st is NULL

SELECT CASE WHEN @ExtraCount = 0 AND @DistinctCount <= 2 THEN SUM(Cost) ELSE 0 END Cost ,Services FROM TestCost s
INNER JOIN (SELECT t.value('.', 'VARCHAR(100)') Value FROM @x.nodes('/A') AS x(t))t ON t.Value = s.Services
GROUP BY Services


--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

Advertisements