SQL Puzzle | The Longest Period – II

This puzzle is simple. Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one.

Sample Inputs

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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

--

CREATE TABLE Employeex
(
	 EmployeeId BIGINT
	,DepartmentId INT
	,FirstName VARCHAR(1)
	,LastName VARCHAR(1)
	,Salary NUMERIC(10,3)
	,HireDate DATETIME 
	,TerminationDate DATETIME 
)
GO

INSERT INTO Employeex VALUES
(1,1000,'A','Z',300000,'2001-09-01 00:00:00.000','2010-01-16 00:00:00.000'),
(2,1000,'B','Y',350000,'2003-03-15 00:00:00.000',NULL),
(3,1000,'C','X',500000,'2010-01-15 00:00:00.000','2015-12-02 00:00:00.000'),
(4,1000,'D','W',400000,'2012-05-15 00:00:00.000',NULL),
(5,1000,'D','Q',200000,'2014-04-15 00:00:00.000','2015-12-02 00:00:00.000'),
(6,1000,'E','P',300000,'2015-08-01 00:00:00.000',NULL),
(7,1000,'F','N',600000,'2015-12-01 00:00:00.000',NULL),
(8,1000,'G','O',700000,'2015-12-01 00:00:00.000',NULL),
(9,1000,'H','P',1000000,'2016-01-01 00:00:00.000',NULL)
GO

--

—

SOLUTION – 1

--

SELECT MAX(dr) [LongestPeriod] FROM 
(
       SELECT DATEDIFF(d, a, ISNULL(LEAD(a) OVER (ORDER BY a),GETDATE())) dr FROM 
       (
             SELECT HireDate a FROM Employeex UNION ALL SELECT TerminationDate FROM Employeex WHERE TerminationDate IS NOT NULL
       )t
)r


--

SOLUTION – 2

--

SELECT MAX(gap) [LongestPeriod] FROM
(
       SELECT DATEDIFF(D, EmpDate, ISNULL(LEAD(EmpDate) OVER (ORDER BY EmpDate),GETDATE())) as gap FROM (
              SELECT EmpDate FROM Employeex
                      UNPIVOT(EmpDate FOR DateVal IN (HireDate, TerminationDate)) AS U
       )TblEmp 
)TempEmp

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com