Tags

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


T-SQL Query | [ The Multiple Condition Puzzle ]

Puzzle Statement

  1. We have a table called “Groups”
  2. I need to pick the group_no’s who has group_name both TEST1 and TEST2 and Active should be 1. In this case I need 100 and 300 as this both group_no contains TEST1 and TEST2 and are active.
  3. Here i don’t get 200 because it has both groupnames but for the 4th record its active status is 0.
  4. Here i dont get 400 because it has only TEST1 but dont have a record with groupname TEST2.
  5. Please check out the sample input and expected output for details.

Sample Input

Group_id Group_no Group_name Active
1 100 TEST1 1
2 100 TEST2 1
3 200 TEST1 1
4 200 TEST2 0
5 300 TEST1 1
6 300 TEST2 1
7 400 TEST1 1

Expected output

Group_id Group_no Group_name Active
1 100 TEST1 1
2 100 TEST2 1
5 300 TEST1 1
6 300 TEST2 1

Rules/Restrictions

  • Your solution should be should use “SELECT” statement or “CTE
  • Your solution should be generic in nature.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Groups
(
	 Group_id INT
	,Group_no INT
	,Group_name VARCHAR(10)
	,Active INT
)
GO

INSERT INTO Groups VALUES
(1, 100, 'TEST1', 1),
(2, 100, 'TEST2', 1),
(3, 200, 'TEST1', 1),
(4, 200, 'TEST2', 0),
(5, 300, 'TEST1', 1),
(6, 300, 'TEST2', 1),
(7, 400, 'TEST1', 1)

--

UPDATE – 23-Apr-2015 – Solution 1


--

;WITH CTE1 AS
(
	SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST1'
)
, CTE2 AS
(
	SELECT Group_no FROM Groups WHERE Active = 1 AND Group_name = 'TEST2'
)
SELECT g.* FROM CTE1 a
INNER JOIN CTE2 b ON a.Group_no = b.Group_no
INNER JOIN Groups g on a.Group_no = g.Group_no

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements