SQL Puzzle | The Greatest number of employees

Write a T-SQL query that returns each employee and for each employee include the greatest number of employees that worked for the company at any time during their tenure and the first date that maximum was reached.

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,'2004-01-15 00:00:00.000','2010-12-02 00:00:00.000'),
(4,1000,'D','W',400000,'2005-05-15 00:00:00.000',NULL),
(5,1000,'D','Q',200000,'2006-04-15 00:00:00.000','2008-12-02 00:00:00.000'),
(6,1000,'E','P',300000,'2007-08-01 00:00:00.000',NULL),
(7,1000,'F','N',600000,'2008-12-01 00:00:00.000',NULL),
(8,1000,'G','O',700000,'2009-12-01 00:00:00.000',NULL),
(9,1000,'H','P',1000000,'2010-01-01 00:00:00.000',NULL),
(11,1000,'J','Z',300000,'2002-09-05 00:00:00.000','2009-12-15 00:00:00.000'),
(10,1000,'I','Z',300000,'2001-09-01 00:00:00.000','2009-12-05 00:00:00.000')

--

—

SOLUTION – 1

--

SELECT * FROM Employeex x
CROSS APPLY
(
       SELECT TOP 1 * FROM
       (
             SELECT d StartDate, Next EndD , SUM(Op) OVER (ORDER BY d) #Employees FROM 
             (
                    SELECT DISTINCT d , ISNULL(LEAD(d) OVER (ORDER BY d), GETDATE()) Next  , Op FROM 
                    (
                           SELECT HireDate d , 1 Op  FROM Employeex
                           UNION ALL
                           SELECT ISNULL(TerminationDate,GETDATE()) TerminationDate , -1 FROM Employeex
                    )r     
             ) cr
       )e
       WHERE e.EndD BETWEEN x.HireDate AND ISNULL(x.TerminationDate,GETDATE())
       ORDER BY #Employees DESC
)r


--

SOLUTION – 2

--


select * from Employeex e1 cross apply
(
       Select top 1 dateadd(month, e.number ,'20000101') dt, cnt1 cnt1 from (select top 10000 row_number() over (order by (select null)) - 1 number 
       from Master..spt_values a cross join Master..spt_values b) e
       cross apply 
       (
             select count(*) cnt1 from Employeex e1 where dateadd(month, e.number ,'20000101') between e1.HireDate and isnull(e1.TerminationDate, getdate())  
       ) A 
       where dateadd(month, e.number ,'20000101') <= getdate() and dateadd(month, e.number ,'20000101') between e1.HireDate and isnull(e1.TerminationDate,getdate())
       order by cnt1 desc, dateadd(month, e.number ,'20000101')
) B


--

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