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
Thanks
Basit.