Tags

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


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

Advertisements