SQL Puzzle | Get department count with department data based on a parameter

In this puzzle we have accept a parameter called @departmentId and fetch data based on it with the total number of employees present in the department. If you get a @departmentId which is not present in the database then you need show count as 0 and other columns as NULL. We also need to add some static XML.Please check the sample input and expected output.

Sample Input

EmployeeID LastName FirstName DepartmentID
1 Ramesh k 1
2 Krishan A 1
3 Avtaar R 1
4 Harish B 2
5 Naga D 2
6 Simpson M 3
7 Mayanka J 5

Expected Output – 1 | Matching Data

cnt EmployeeID LastName FirstName DepartmentID
3 1 Ramesh k 1
3 2 Krishan A 1
3 3 Avtaar R 1

Expected Output – 2 | NON – Matching Data

cnt EmployeeID LastName FirstName DepartmentID
0 NULL NULL NULL NULL

Script

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

 ```-- CREATE TABLE [TestEmployees] ( [EmployeeID] [int] NOT NULL, [LastName] [Nvarchar](50) NULL, [FirstName] [varchar](50) NULL, [DepartmentID] [int] NULL ) GO INSERT [TestEmployees] ([EmployeeID], [LastName], [FirstName], [DepartmentID]) VALUES (1, 'Ramesh', N'k', 1) ,(2, 'Krishan', N'A', 1) ,(3, 'Avtaar', N'R', 1) ,(4, 'Harish', N'B', 2) ,(5, 'Naga', N'D', 2) ,(6, 'Simpson', N'M', 3) ,(7, 'Mayanka', N'J', 5) -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ```-- DECLARE @DepartmentID AS INT = 1 SELECT * FROM ( SELECT COUNT([EmployeeID]) cnt FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )x LEFT JOIN ( SELECT * FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )y ON 1 = 1 -- ```

Output – 1 | Matching Data

 ```-- /*------------------------ DECLARE @DepartmentID AS INT = 1 SELECT * FROM ( SELECT COUNT([EmployeeID]) cnt FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )x LEFT JOIN ( SELECT * FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )y ON 1 = 1 ------------------------*/ cnt EmployeeID LastName FirstName DepartmentID ----------- ----------- -------------- --- ------------- -- ------------ 3 1 Ramesh k 1 3 2 Krishan A 1 3 3 Avtaar R 1 (3 row(s) affected) -- ```

Output – 2 | NON Matching Data

 ```-- /*------------------------ DECLARE @DepartmentID AS INT = 7 SELECT * FROM ( SELECT COUNT([EmployeeID]) cnt FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )x LEFT JOIN ( SELECT * FROM [TestEmployees] WHERE [DepartmentID] = @DepartmentID )y ON 1 = 1 ------------------------*/ cnt EmployeeID LastName FirstName DepartmentID ----------- ----------- ------------- ---- ------------ ---- ------------ 0 NULL NULL NULL NULL (1 row(s) affected) -- ```

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