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