Tags

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


SQL Server Puzzle | Zero Hiring Years Puzzle

Find all the year based intervals from 1975 up to current when the company did not hire employees. Use a single SELECT statement against employee table.

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

Sample Input

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

Expected Output

Years
1978-1978
1981-1981
1986-1989
1991-1996
1998-2016

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 | USING Except & Numbers Table


--


SELECT CONCAT( MIN(HireDate) , '-' ,  MAX(HireDate) ) Years 
FROM 
(
	SELECT HireDate, HireDate - ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Rnk 
        FROM
	(
	
		SELECT DISTINCT Number HireDate
		FROM MASTER..SPT_VALUES
		WHERE Number >= 1975 AND Number <= YEAR(GETDATE()) 
                EXCEPT 
                SELECT DISTINCT YEAR(HIRE_DATE) HireDate FROM [dbo].[EMPLOYEES_N] 
                WHERE HIRE_DATE >= '01/01/1975'
	)u
)t
GROUP BY Rnk


--

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