Tags

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


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

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 2004-01-15 00:00:00.000 2010-12-02 00:00:00.000
4 1000 D W 400000.000 2005-05-15 00:00:00.000 NULL
5 1000 D Q 200000.000 2006-04-15 00:00:00.000 2008-12-02 00:00:00.000
6 1000 E P 300000.000 2007-08-01 00:00:00.000 NULL
7 1000 F N 600000.000 2008-12-01 00:00:00.000 NULL
8 1000 G O 700000.000 2009-12-01 00:00:00.000 NULL
9 1000 H P 1000000.000 2010-01-01 00:00:00.000 NULL
11 1000 J Z 300000.000 2002-09-05 00:00:00.000 2009-12-15 00:00:00.000
10 1000 I Z 300000.000 2001-09-01 00:00:00.000 2009-12-05 00:00:00.000

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

Advertisements