Get result from multiple tables employee Year wise in SQL Server

Last Reply on Oct 04, 2017 01:46 AM By dharmendr

Posted on Sep 26, 2017 02:21 AM

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.

You are viewing reply posted by: dharmendr on Oct 04, 2017 01:46 AM.
Posted on Oct 04, 2017 01:46 AM

You can't ask multiple queries within a question. This is against forum rules and necessary to maintain clean forum.
It is requested Mark Answer the replies when question is answered and ask a new question as a responsible member to help fellow programmers around the world.
Note: If you don't mark answers, people will loose interest in your questions.