SQLTeam.com | Weblogs | Forums

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

sql2008r2

#1

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 2014 then in Last_Designation,Promoted_Designation, Last_Gross and Promoted_Gross need to be same as 2012 or previous one.

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','2013-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

O/P will be

Thanks
Basit.