SQL Puzzle | The Completed Part Puzzle

In this puzzle you have to find out which part is finished(Completed). If the completed columns value is 1 for any part that means that the part is completed. We have to find out how many entries are there for each part and which one is completed. We have to print C in case any part is finished(Completed).

Please check out the sample input values and sample expected output below.

Can you do that in a single select ?

Sample Input

Expected Output

Script

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

--

CREATE TABLE Parts
(
	PartNm VARCHAR(50) NULL,
	Completed BIT NULL
)
GO

INSERT INTO Parts (PartNm,Completed)
VALUES 
('PartA',0),
('PartA',1),
('PartB',0),
('PartC',0),
('PartC',0)

--
--

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

--
 
SELECT 
		PartNm,
		COUNT(*) NumberOfRecords ,
		CASE WHEN SUM(CAST(Completed AS INT)) = '1' THEN 'C' ELSE '' END Completed
FROM Parts 
GROUP BY PartNm

--

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