--


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


--

--


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


--