Tags

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


SQL Puzzle | The YesNo Puzzle

Puzzle Statement

In this puzzle you have to accept an input parameter. The value of the input parameter can be Yes or No. The input table contains 2 columns Id, Bits.

If the user passes No value as the input parameter then we should get all Ids where at least one Bits value is No. E.g. For Id = 1 we have bits Yes & No. So all distinct Ids = 1 should come as the output.

If the user passes Yes value as the input parameter then we should get all Ids where we don’t have a single Bits value as No. E.g. For Id = 2 we have don’t have a single bits value as No. So Id = 2 should come as the output.

Please checkout Sample input and expected output for Yes and No.

1 Yes
2 Yes
1 No
1 No
3 No
4 Yes

Expected Output

For Yes

2 Yes
4 Yes

For No

1 Yes
1 No
3 No

Script

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

--


CREATE TABLE YesNo
(
	 Id SmallInt
	,Bits VarChar(3)
)
GO

INSERT INTO YesNo
VALUES
(1, 'Yes'),
(2, 'Yes'),
(1, 'No'),
(1, 'No'),
(3, 'No'),
(4, 'Yes')
GO

SELECT * FROM YesNo
GO


--

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

SOLUTION 1 | Using Except & Intersect

--

DECLARE @Input AS VARCHAR(3) = 'Yes'

IF @Input = 'Yes'
	BEGIN
		SELECT DISTINCT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo
			EXCEPT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
	END
ELSE IF @Input = 'No'
	BEGIN
		SELECT u.* FROM YesNo u
		INNER JOIN
		(
			SELECT ID FROM YesNo WHERE Bits = 'Yes'
			INTERSECT
			SELECT ID FROM YesNo WHERE Bits = 'No'
		)y
		ON y.Id = u.Id
		UNION
		SELECT ID , Bits FROM YesNo WHERE Bits = 'No'
	END

--

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