Tags

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


SQL PUZZLE | The Group Status Puzzle ? | A SINGLE SELECT Interview question

In this puzzle you have to group data based on the Id and QTY column & find out the status for them, if the status are multiple then we need “In progress in this column” otherwise you just need to place the same status if all the status are same. The challenge is to do this in a single SELECT. Please see the sample input and expected output.

Sample-1

Id A1 QTY Vals
1 A 25 OPEN
1 B 25 OPEN
1 C 25 OPEN
1 D 25 CLOSE
1 E 25 OPEN
1 F 25 OPEN
1 G 25 OPEN
2 A 43 OPEN
2 B 43 OPEN
2 C 43 OPEN
2 D 43 OPEN
2 E 43 OPEN
2 F 43 OPEN
2 G 43 OPEN
3 A 20 CLOSE
3 B 20 CLOSE
3 C 20 CLOSE
3 D 20 CLOSE
3 E 20 CLOSE
3 F 20 CLOSE
3 G 20 CLOSE

Expected output-1

Id QTY GrStatus
1 25 In-Progress
2 43 OPEN
3 20 CLOSE

Script – DDL and INSERT sample data

--

CREATE TABLE GroupedStatus
(
	   Id INT
	,  A1 VARCHAR(1)
	,  QTY INT   
	,  Vals VARCHAR(10)
)
GO

INSERT INTO GroupedStatus VALUES
(1,'A',25,'OPEN'),
(1,'B',25,'OPEN'),
(1,'C',25,'OPEN'),
(1,'D',25,'CLOSE'),
(1,'E',25,'OPEN'),
(1,'F',25,'OPEN'),
(1,'G',25,'OPEN'),
(2,'A',43,'OPEN'),
(2,'B',43,'OPEN'),
(2,'C',43,'OPEN'),
(2,'D',43,'OPEN'),
(2,'E',43,'OPEN'),
(2,'F',43,'OPEN'),
(2,'G',43,'OPEN'),
(3,'A',20,'CLOSE'),
(3,'B',20,'CLOSE'),
(3,'C',20,'CLOSE'),
(3,'D',20,'CLOSE'),
(3,'E',20,'CLOSE'),
(3,'F',20,'CLOSE'),
(3,'G',20,'CLOSE')
GO

SELECT * FROM GroupedStatus
GO

--

SOLUTION – 1

--

SELECT g.Id , g.QTY , IIF(COUNT(DISTINCT Vals)>1,'In-Progress',MAX(Vals)) GrStatus
FROM GroupedStatus g
GROUP BY g.Id , g.QTY
ORDER BY g.Id

--

Output – 1

--

Id          QTY         GrStatus
----------- ----------- -----------
1           25          In-Progress
2           43          OPEN
3           20          CLOSE

(3 rows affected)

--

SOLUTION – 2

--

SELECT g.Id , g.QTY , IIF(MIN(Vals)MAX(Vals),'In-Progress',MAX(Vals)) GrStatus
FROM GroupedStatus g
GROUP BY g.Id , g.QTY
ORDER BY g.Id

--

Output – 2

--

Id          QTY         GrStatus
----------- ----------- -----------
1           25          In-Progress
2           43          OPEN
3           20          CLOSE

(3 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com