Tags

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


SQL Server Puzzle | Hiring Years Puzzle

In this puzzle, You have to write a SELECT statement that would show the years of hire in each department. The Output should have 3 columns [See expected output]: Deptno, Year1, and Year2.

Year1 will have the first year of hiring.

Year2 will have 2nd of year of hiring if there are no hiring after that. Else if there are more number of hiring years, show “Many (N)”. This n should not include the first year of hiring.

Please check out the sample input and the expected output below-

Sample Input

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE DEPARTMENT_ID
1001 Pawan Khowal 1975-02-21 10
1002 Ramesh Krishan 1976-02-21 11
1003 Avtaar Kishan 1977-02-21 12
1004 Marank Tripathi 1979-02-21 13
1007 Prem K 1980-02-21 15
1008 Ganesh Pawade 1979-02-21 14
1016 Qaue K 1975-02-21 16
1024 Veeru K 1975-02-21 11
1025 Wasil K 1975-02-21 18
1027 Ernst Young 1980-02-21 13
1046 Xhera K 1980-02-21 17
1052 Xerra K 1982-02-21 16
1073 Yash K 1983-02-21 17
1084 Zahar H 1984-02-21 12
1094 Queen Bhardwaj 1985-02-21 12
1102 Suresh K 1975-02-21 10
1103 Tisha K 1975-02-21 11
1104 Umesh K 1972-02-21 12
1116 Ashish Thakre 1990-02-21 13
1155 Rahil K 1975-02-21 17
1207 Wahim K 1974-02-21 10
1225 Bushan Pawade 1997-02-21 13

Expected Output

DeptNo Year 1 Year 2
10 1974 Many ( 2 )
11 1975 Many ( 2 )
12 1972 Many ( 3 )
13 1979 Many ( 3 )
14 1979
15 1980
16 1975 1982
17 1975 Many ( 2 )
18 1975

Rules/Restrictions

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

Script | use below script to create table & insert some sample data


--


CREATE TABLE [dbo].[EMPLOYEES_N]
(
	[EMPLOYEE_ID] [int] NOT NULL,
	[FIRST_NAME] [varchar](20) NULL,
	[LAST_NAME] [varchar](25) NOT NULL,
	[EMAIL] [varchar](25) NOT NULL,
	[PHONE_INT] [varchar](20) NULL,
	[HIRE_DATE] [date] NOT NULL,
	[JOB_ID] [varchar](10) NOT NULL,
	[SALARY] [int] NULL,
	[COMMISSION_PCT] [int] NULL,
	[MANAGER_ID] [int] NULL,
	[DEPARTMENT_ID] [int] NULL,
        CONSTRAINT [EMP_EMP_ID_PK] PRIMARY KEY CLUSTERED 
	(
		[EMPLOYEE_ID] ASC
	)
)
GO

INSERT INTO [dbo].[EMPLOYEES_N]
VALUES
(1001,'Pawan','Khowal','Pawan.Khowal@a.com', 9863232, '2/21/1975', 2 ,12000, 15, NULL, 10 ),
(1002,'Ramesh','Krishan','Ramesh.K@a.com', 9863292, '2/21/1976', 2 ,13000, 1, NULL, 11 ),
(1003,'Avtaar','Kishan','Avtaar.K@a.com', 986322, '2/21/1977', 2 ,12320, 2, NULL, 12 ),
(1004,'Marank','Tripathi','Marank.K@a.com', 981222, '2/21/1979',2 , 12230, 3, NULL, 13 ),
(1008,'Ganesh','Pawade','Ganesh.p@a.com', 9860032, '2/21/1979', 2 ,10000, 5, NULL, 14 ),
(1007,'Prem','K','Prem.K@a.com', 986312, '2/21/1980', 2 ,2000, 9, NULL, 15 ),
(1016,'Qaue','K','Qaue.K@a.com', 98632, '2/21/1975', 2 ,4000, 9, NULL, 16 ),
(1155,'Rahil','K','Rahil.K@a.com', 98692, '2/21/1975', 2 ,56000, 10, NULL, 17 ),
(1102,'Suresh','K','Suresh.K@a.com', 9862, '2/21/1975', 2 ,7000, 3, NULL, 10 ),
(1103,'Tisha','K','Tisha.K@a.com', 98621, '2/21/1975', 2 ,5600, 5, NULL, 11 ),
(1104,'Umesh','K','Umesh.K@a.com', 9864532, '2/21/1972', 2 ,6600, 6, NULL, 12 ),
(1024,'Veeru','K','Veeru.K@a.com', 981231232, '2/21/1975', 2 ,5000, 9, NULL, 11 ),
(1207,'Wahim','K','Wahim.K@a.com', 9232, '2/21/1974', 2 ,12000, 10, NULL, 10 ),
(1046,'Xhera','K','Xhera.K@a.com', 9863232, '2/21/1980', 2 ,12000, 15, NULL, 17 ),
(1025,'Wasil','K','Wasil.K@a.com', 97653232, '2/21/1975', 2 ,12123, 0.5, NULL, 18 ),
(1052,'Xerra','K','Xerra.K@a.com', 912232, '2/21/1982', 2 ,1204, 6, NULL, 16 ),
(1073,'Yash','K','Yash.K@a.com', 984343232, '2/21/1983', 2 ,12230, 7, NULL, 17 ),
(1084,'Zahar','H','Zahar.H@a.com', 98565232, '2/21/1984', 2 ,12890, 15, NULL, 12 ),
(1094,'Queen','Bhardwaj','Queen.B@a.com', 983332, '2/21/1985', 2 ,1110, 0.5, NULL, 12 ),
(1027,'Ernst','Young','Ernst.Y@a.com', 9834435232, '2/21/1980', 2 ,121100, 6.5, NULL, 13 ),
(1116,'Ashish','Thakre','Ashish.T@a.com', 98632434, '2/21/1990', 2 ,12340, 15, NULL, 13 ),
(1225,'Bushan','Pawade','Bhushan.P@a.com', 9863245, '2/21/1997', 2 ,12000, 5, NULL, 13 )
GO


--

Solution 1 


--

SELECT DEPARTMENT_ID DeptNo, MIN(YEAR(HIRE_DATE)) [Year 1]
,CASE WHEN COUNT(*) -1 = 0 THEN ''
	  WHEN COUNT(*) -1 = 1 THEN CAST(MAX(y) AS VARCHAR(10))
	  WHEN COUNT(*) -1 >= 2 THEN CONCAT(' Many ( ', COUNT(*) -1 , ' )' )
END [Year 2]
FROM [dbo].[EMPLOYEES_N] b
OUTER APPLY
(
	SELECT TOP 1 YEAR(HIRE_DATE) y
	FROM [dbo].[EMPLOYEES_N] a
	WHERE a.DEPARTMENT_ID = b.DEPARTMENT_ID	
	AND YEAR(b.HIRE_DATE) < YEAR(a.HIRE_DATE)
)r
GROUP BY DEPARTMENT_ID

--

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