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

EmployeeId DepartmentId FirstName LastName Salary HireDate TerminationDate
1 1000 A Z 300000.000 2001-09-01 00:00:00.000 2010-01-16 00:00:00.000
2 1000 B Y 350000.000 2003-03-15 00:00:00.000 NULL
3 1000 C X 500000.000 2010-01-15 00:00:00.000 2015-12-02 00:00:00.000
4 1000 D W 400000.000 2012-05-15 00:00:00.000 NULL
5 1000 D Q 200000.000 2014-04-15 00:00:00.000 2015-12-02 00:00:00.000
6 1000 E P 300000.000 2015-08-01 00:00:00.000 NULL
7 1000 F N 600000.000 2015-12-01 00:00:00.000 NULL
8 1000 G O 700000.000 2015-12-01 00:00:00.000 NULL
9 1000 H P 1000000.000 2016-01-01 00:00:00.000 NULL

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