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