Hi, 

 
How to get the year wise employee as per the promotion.

Example: Employee Number 'A-001' Join on '01-07-2012' then the O/P will be showing in below.

As Employee Number 'A-001' there is no promotion in 2012 & 2013 then in Last_Designation,Promoted_Designation, Last_Gross and Promoted_Gross need to be same as 2014 or previous one. in 2012 & 2013

Below is my SQL Query with Data.

 
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_CurrentDesignation] [nvarchar](50) NULL,
[Emp_CurrentGross] [numeric](18, 0) NULL
) ON [PRIMARY]
GO
INSERT INTO [UserDB].[dbo].[Employee]
([Emp_No]
,[Emp_Number]
,[Emp_Name]
,[Emp_JoiningDate]
,[Emp_ResignDate]
,[Emp_Status]
,[Emp_CurrentDesignation]
,[Emp_CurrentGross])
VALUES
(1,'A-001','Alex','2012-07-01',null,'On Board','Trainee3',2000)
GO
INSERT INTO [UserDB].[dbo].[Employee]
([Emp_No]
,[Emp_Number]
,[Emp_Name]
,[Emp_JoiningDate]
,[Emp_ResignDate]
,[Emp_Status]
,[Emp_CurrentDesignation]
,[Emp_CurrentGross])
VALUES
(2,'A-002','Smith','2014-07-01','2015-07-01','Resigned','HR1',1500)
GO
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
INSERT INTO [UserDB].[dbo].[Promotion]
([Prom_No]
,[Prom_EmpNo]
,[Last_Designation]
,[Promoted_Designation]
,[WEF_Date]
,[Promoted_Gross]
,[Last_Gross])
VALUES
(1,1,'Trainee1','Trainee2','2014-11-01',1000,500)
GO
INSERT INTO [UserDB].[dbo].[Promotion]
([Prom_No]
,[Prom_EmpNo]
,[Last_Designation]
,[Promoted_Designation]
,[WEF_Date]
,[Promoted_Gross]
,[Last_Gross])
VALUES
(2,1,'Trainee2','Trainee3','2015-03-01',2000,1000)
GO
 
 below output should be
Year Emp_No Emp_Number Emp_Name Emp_JoiningDate Emp_ResignDate Emp_Status Emp_CurrentDesignation Emp_CurrentGross Last_Designation Promoted_Designation Last_Gross Promoted_Gross
2012 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee1 Trainee2 500 1000
2013 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee1 Trainee2 500 1000
2014 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee1 Trainee2 500 1000
2014 2 A-002 Smith 07/01/2014 07/01/2015 Resigned HR1 1500 HR1 HR1 1500 1500
2015 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee2 Trainee3 1000 2000
2015 2 A-002 Smith 07/01/2014 07/01/2015 Resigned HR1 1500 HR1 HR1 1500 1500
2016 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee2 Trainee3 1000 2000
2017 1 A-001 Alex 07/01/2012 NULL On Board Trainee3 2000 Trainee2 Trainee3 1000 2000
 
looking attached output in excel. 
 
 Thanks
Basit.