SQL Server Puzzle | NO Hiring Years Puzzle | Improving my SQL BI Skills

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

2 thoughts on “SQL Server Puzzle | NO Hiring Years Puzzle”

Gregor Kralj said:

May 16, 2019 at 2:30 PM

with cte as (
select YEAR(HIRE_DATE) as HIRE_DATE, LEAD(YEAR(HIRE_DATE)) over (order by HIRE_DATE) as NEXT_HIRE_DATE
from EMPLOYEES_N
)
,cte2 as
(
select CONCAT(HIRE_DATE +1, ‘ – ‘, NEXT_HIRE_DATE – 1) as Years from cte
where NEXT_HIRE_DATE – HIRE_DATE > 1 and HIRE_DATE > 1975
)
select * from cte2

LikeLiked by 1 person

revank219 said:

June 3, 2021 at 1:50 PM

;with cte as (
select YEAR(Hire_date)Hiredate,
ISNULL(Lead(Year(HIRE_DATE))over(order by Hire_date),2021)as Next_hiredate
from [dbo].[EMPLOYEES_New3] where YEAR(HIRE_DATE)>1975
),cte2 as (
select *,case when hiredate+1 = Next_hiredate then 0
when hiredate = Next_hiredate then 0
else 1 end as grouper from cte
)
select concat(Hiredate+1,’-‘,Next_hiredate-1)as Years from cte2 where grouper = 1

LikeLike

Share this

Related

2 thoughts on “SQL Server Puzzle | NO Hiring Years Puzzle”

Leave a comment Cancel reply