Get result from multiple tables employee Year wise in SQL Server

Last Reply on Oct 04, 2017 01:46 AM By dharmendr

Posted on Sep 26, 2017 02:21 AM

Hi,

How to get the employee YearWise and get the other details from Promotion table accordingly.

The employee Table is given below.

CREATE TABLE [dbo].[Employee](
	[Emp_No] [numeric](18, 0) NULL,
	[Emp_Number] [nvarchar](50) NULL,
	[Emp_Name] [nvarchar](50) NULL,
	[Emp_JoiningDate] [date] NULL,
	[Emp_ResignDate] [date] NULL,
	[Emp_Status] [nvarchar](50) NULL,
	[Emp_Designation] [nvarchar](50) NULL,
	[Emp_Gross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

the Data is

 

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           1
           ,'A-001'
           ,'Alex'
           ,'2013-01-01'
           ,'2013-08-24'
           ,'Resigned','Trainee',200)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           2
           ,'A-002'
           ,'Adam'
           ,'2013-01-01'
           ,null
           ,'On Board','HRM',400)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           3
           ,'A-003'
           ,'Maxwell'
           ,'2014-01-01'
           ,null
           ,'On Board','FM',600)
GO

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status],Emp_Designation,Emp_Gross)
     VALUES(
           4
           ,'A-004'
           ,'Smith'
           ,'2014-01-01'
           ,'2014-08-20'
           ,'Resigned','CEO',1000)
GO

Promotion Table structure is

 

 

CREATE TABLE [dbo].[Promotion](
	[Prom_No] [numeric](18, 0) NULL,
	[Prom_EmpNo] [numeric](18, 0) NULL,
	[Last_Designation] [nvarchar](500) NULL,
	[Promoted_Designation] [nvarchar](500) NULL,
	[WEF_Date] [date] NULL,
	[Promoted_Gross] [numeric](18, 0) NULL,
	[Last_Gross] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

the Data is

INSERT INTO [Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (1,2,'HRM Assitant','HRM','2014-01-01',400,200)          

GO
INSERT INTO [Promotion]
           ([Prom_No]
           ,[Prom_EmpNo]
           ,[Last_Designation]
           ,[Promoted_Designation]
           ,[WEF_Date]
           ,[Promoted_Gross]
           ,[Last_Gross])
     VALUES
           (2,3,'Accountant','FM','2015-01-01',600,300)          

GO

The O/P should be

This DATA will come from Employee Table This Data will come from Promotion table if there else employee table
Year Emp_No Em_Number Emp_Name Emp_Joining Emp_Resigned Emp_Status Designation Gross
2013 1 A-001 Alex 01-01-2013 24-08-2013 Resigned Trainee 200
2013 2 A-002 Adam 01-01-2013   OnBoard HRM Assitant 200
2014 2 A-002 Adam 01-01-2013   OnBoard HRM 400
2014 3 A-003 Maxwel 01-01-2014   OnBoard Accountant 300
2014 4 A-004 Smith 01-01-2014 20-08-2014 Resigned CEO 100
2015 2 A-002 Adam 01-01-2013   OnBoard HRM 400
2015 3 A-003 Maxwel 01-01-2014   OnBoard FM 600
2016 2 A-002 Adam 01-01-2013   OnBoard HRM 400
2016 3 A-003 Maxwel 01-01-2014   OnBoard FM 600
2017 2 A-002 Adam 01-01-2013   OnBoard HRM 400
2017 3 A-003 Maxwel 01-01-2014   OnBoard FM 600

Thanks

Basit.

Posted on Sep 28, 2017 02:53 AM

Hi basit0079,

If employee joins in any of year and ounces he gets promoted for the other designation then it’s his current designation till he resigns.

so you need to make sure the output will be if employee is not Promoted yet then you have to get Designation and Gross details from Employee table else in any of year he get promoted and current year is running or later than the promoted year then current designation and gross from the Promotion year as it’s his current Designation and current gross.

Check below test query for your reference.

SQL

CREATE TABLE #Employee ([Emp_No] [numeric](18, 0) NULL,[Emp_Number] [nvarchar](50) NULL,[Emp_Name] [nvarchar](50) NULL,[Emp_JoiningDate] [date] NULL,[Emp_ResignDate] [date] NULL,[Emp_Status] [nvarchar](50) NULL,[Emp_Designation] [nvarchar](50) NULL,[Emp_Gross] [numeric](18, 0) NULL)
INSERT INTO #Employee ([Emp_No],[Emp_Number] ,[Emp_Name] ,[Emp_JoiningDate]  ,[Emp_ResignDate] ,[Emp_Status],Emp_Designation,Emp_Gross)
SELECT 1,'A-001','Alex','2013-01-01','2013-08-24','Resigned','Trainee',200
UNION ALL
SELECT 2,'A-002','Adam','2013-01-01',null,'On Board','HRM',400
UNION ALL
SELECT 3,'A-003','Maxwell','2014-01-01',null,'On Board','FM',600
UNION ALL
SELECT 4,'A-004','Smith','2014-01-01','2014-08-20','Resigned','CEO',1000

CREATE TABLE #Promotion([Prom_No] [numeric](18, 0) NULL,[Prom_EmpNo] [numeric](18, 0) NULL,[Last_Designation] [nvarchar](500) NULL,[Promoted_Designation] [nvarchar](500) NULL,[WEF_Date] [date] NULL,[Promoted_Gross] [numeric](18, 0) NULL,[Last_Gross] [numeric](18, 0) NULL)
INSERT INTO #Promotion 
SELECT 1,2,'HRM Assitant','HRM','2014-01-01',400,200
UNION ALL
SELECT 2,3,'Accountant','FM','2015-01-01',600,300

DECLARE @start_dt DATE=(
select cast(dateadd(year,datediff(year,0,min([emp_joiningdate])),0) as date)
from #employee
) 
 
;WITH cte_dt(dt)
AS (SELECT TOP(DATEPART(YEAR,CURRENT_TIMESTAMP)-DATEPART(YEAR,@start_dt)+1)
			DATEADD(YEAR,ROW_NUMBER() OVER(ORDER BY (SELECT null))-1,@start_dt)
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
) AS tally1(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally2(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally3(n)
)

SELECT DATEPART(YEAR,y.dt) AS [year]
,e.emp_no
,e.emp_number
,e.emp_name
,e.emp_joiningdate
,e.emp_resigndate
,e.emp_status
,(CASE WHEN EXISTS(SELECT [Prom_No] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
		THEN (SELECT [Last_Designation] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
		ELSE (SELECT em.Emp_Designation FROM #employee em WHERE em.emp_no = e.emp_no)
		END)  AS Designation

,(CASE WHEN EXISTS(SELECT [Prom_No] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
		THEN (SELECT [Last_Gross] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
		ELSE (SELECT Emp_Gross FROM #employee em WHERE em.emp_no = e.emp_no)
		END)  AS Gross
FROM cte_dt AS y
inner join #employee AS e
ON e.emp_joiningdate<=y.dt
and ISNULL(e.emp_resigndate,CURRENT_TIMESTAMP)>=y.dt

DROP TABLE #Employee
DROP TABLE #Promotion

OutPut

year emp_no emp_number emp_name emp_joiningdate emp_resigndate emp_status Designation Gross
2013 1 A-001 Alex 2013-01-01 2013-08-24 Resigned Trainee 200
2013 2 A-002 Adam 2013-01-01 NULL On Board HRM 400
2014 2 A-002 Adam 2013-01-01 NULL On Board HRM Assitant 200
2014 3 A-003 Maxwell 2014-01-01 NULL On Board FM 600
2014 4 A-004 Smith 2014-01-01 2014-08-20 Resigned CEO 1000
2015 2 A-002 Adam 2013-01-01 NULL On Board HRM Assitant 200
2015 3 A-003 Maxwell 2014-01-01 NULL On Board Accountant 300
2016 2 A-002 Adam 2013-01-01 NULL On Board HRM Assitant 200
2016 3 A-003 Maxwell 2014-01-01 NULL On Board Accountant 300
2017 2 A-002 Adam 2013-01-01 NULL On Board HRM Assitant 200
2017 3 A-003 Maxwell 2014-01-01 NULL On Board Accountant 300

Posted on Oct 02, 2017 07:30 AM Modified on on Oct 02, 2017 07:31 AM

hi basit0079,

You need to check year of emp_joiningdate less than equal to y.dt year value as you are assuming y.dt value as first starting date of the employee joined date. so if employee join in between july or dec will not satisfy the date condition which we are checking for on condition of Inner join.

so just modify the below ON condition in he test query it will work fine. 

ON e.emp_joiningdate<=y.dt
-- Replace above ON condition with As below line
ON YEAR(e.emp_joiningdate) <=YEAR(y.dt)