Tags

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


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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

Advertisements