Get result from multiple tables employee Year wise in SQL Server

basit007...

SOLVED CLOSED ACTIVE ANSWERED
User: basit0079
Posted: on Sep 26, 2017 02:21 AM
Forum: SQL Server
Answer: 2
Views: 4852

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.

Download FREE API for Word, Excel and PDF in ASP.Net

basit007...

0 4

Reply
User: basit0079
Replied: on Sep 28, 2017 03:46 AM
Report

Many Thanks,

can you please check with below data showing error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

CREATE TABLE #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)
INSERT INTO #Employee ([Emp_No],[Emp_Number] ,[Emp_Name] ,[Emp_JoiningDate]  ,[Emp_ResignDate] ,[Emp_Status],Emp_Designation,Emp_Gross)
SELECT 1,'A-001','Alex','2012-09-19',null,'On Board','Trainee3',2000

 
CREATE TABLE #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)
INSERT INTO #Promotion
SELECT 1,1,'Trainee1','Trainee2','2013-11-01',1000,500
UNION ALL
SELECT 2,1,'Trainee2','Trainee3','2014-03-01',2000,1000
 
DECLARE @start_dt DATE=(
select cast(dateadd(year,datediff(year,0,min([emp_joiningdate])),0) as date)
from #employee
)
  
;WITH cte_dt(dt)
AS (SELECT TOP(DATEPART(YEAR,CURRENT_TIMESTAMP)-DATEPART(YEAR,@start_dt)+1)
            DATEADD(YEAR,ROW_NUMBER() OVER(ORDER BY (SELECT null))-1,@start_dt)
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
) AS tally1(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally2(n)
cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as tally3(n)
)
 
SELECT DATEPART(YEAR,y.dt) AS [year]
,e.emp_no
,e.emp_number
,e.emp_name
,e.emp_joiningdate
,e.emp_resigndate
,e.emp_status
,(CASE WHEN EXISTS(SELECT [Prom_No] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
        THEN (SELECT [Last_Designation] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
        ELSE (SELECT em.Emp_Designation FROM #employee em WHERE em.emp_no = e.emp_no)
        END)  AS Designation
 
,(CASE WHEN EXISTS(SELECT [Prom_No] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
        THEN (SELECT [Last_Gross] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]))
        ELSE (SELECT Emp_Gross FROM #employee em WHERE em.emp_no = e.emp_no)
        END)  AS Gross
FROM cte_dt AS y
inner join #employee AS e
ON e.emp_joiningdate<=y.dt
and ISNULL(e.emp_resigndate,CURRENT_TIMESTAMP)>=y.dt
 
DROP TABLE #Employee
DROP TABLE #Promotion

Thanks

Basit

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Get result from multiple tables employee Year wise in SQL Server

Company

Explore

Follow us

Contact Us