How to get the employee YearWise and get the other details f

Question

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