Question

How do you show the count of joinees of each department for the most recent month having atleast one joinee? For example, in Department1, In January 10 members and in February 5 members joined, show it as 5 members joined in February For example, in Department2, In January 4 members and in no one joined in February, show it as 4 members joined in January

Answer

CREATE TABLE department 
(
 id INT IDENTITY,
 name VARCHAR(20)
)

CREATE TABLE employee
(
id INT IDENTITY,
name VARCHAR(20),
departmentID INT ,
DOJ DATE
)


INSERT INTO [dbo].[department]
           ([name])
     VALUES ('Accounting'),         
            ('Human Resource'),
            ('Development'),
           ('Finance')


INSERT INTO [dbo].[employee]
           ([name]
           ,[departmentID]
           ,[DOJ])
     VALUES
           ('Rami',1,'2/6/2012'),
           ('Reddy',1,'1/8/2012'),
           ('Ravi',1,'2/3/2012'),
           ('Kirshore',1,'11/30/2011'),
           ('Kumar',2,'12/8/2011'),
           ('Praveen',2,'1/8/2012'),
           ('Vallavan',2,'1/8/2012'),
           ('ManiKuttan',2,'1/2/2012')

;WITH cte AS 
(
select  ROW_NUMBER() OVER (PARTITION BY department.name 
    						ORDER BY YEAR(DOJ) DESC,MONTH(DOJ) desc) AS serial , 
    	department.name,COUNT(employee.name) AS cnt, 
MONTH(DOJ) AS MonthDOJ ,YEAR(DOJ) AS YrDOJ
FROM department 
INNER JOIN
employee ON department.id = employee.departmentID 
GROUP BY department.name,
MONTH(DOJ),YEAR(DOJ)
)
SELECT * FROM CTE WHERE SERIAL =1

Read More..   [32134 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  ·