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: 4845

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

kalpesh

0 1

Reply
User: kalpesh
Replied: on Sep 26, 2017 02:30 AM
Report

Please Share your SQL Query

basit007...

0 2

Reply
User: basit0079
Replied: on Sep 27, 2017 08:36 AM
Report

Please see the below query, i want to check in Promotion transaction, if there is no data available in promotion then put the latest designation and gross from Employee master else from promotion master last_Designation and Last_Gross.

Many Thanks once again.

Thanks

Basit.

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 y
,e.emp_no
,e.emp_number
,e.emp_name
,e.emp_joiningdate
,e.emp_resigndate
,e.emp_status
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

kalpesh

0 3

Reply
ANSWER
User: kalpesh
Replied: on Sep 28, 2017 02:53 AM
Report

Hi basit0079,

If employee joins in any of year and ounces he gets promoted for the other designation then it’s his current designation till he resigns.

so you need to make sure the output will be if employee is not Promoted yet then you have to get Designation and Gross details from Employee table else in any of year he get promoted and current year is running or later than the promoted year then current designation and gross from the Promotion year as it’s his current Designation and current gross.

Check below test query for your reference.

SQL

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','2013-01-01','2013-08-24','Resigned','Trainee',200
UNION ALL
SELECT 2,'A-002','Adam','2013-01-01',null,'On Board','HRM',400
UNION ALL
SELECT 3,'A-003','Maxwell','2014-01-01',null,'On Board','FM',600
UNION ALL
SELECT 4,'A-004','Smith','2014-01-01','2014-08-20','Resigned','CEO',1000

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,2,'HRM Assitant','HRM','2014-01-01',400,200
UNION ALL
SELECT 2,3,'Accountant','FM','2015-01-01',600,300

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

OutPut

year	emp_no	emp_number	emp_name	emp_joiningdate	emp_resigndate	emp_status	Designation	Gross
2013	1	A-001	Alex	2013-01-01	2013-08-24	Resigned	Trainee	200
2013	2	A-002	Adam	2013-01-01	NULL	On Board	HRM	400
2014	2	A-002	Adam	2013-01-01	NULL	On Board	HRM Assitant	200
2014	3	A-003	Maxwell	2014-01-01	NULL	On Board	FM	600
2014	4	A-004	Smith	2014-01-01	2014-08-20	Resigned	CEO	1000
2015	2	A-002	Adam	2013-01-01	NULL	On Board	HRM Assitant	200
2015	3	A-003	Maxwell	2014-01-01	NULL	On Board	Accountant	300
2016	2	A-002	Adam	2013-01-01	NULL	On Board	HRM Assitant	200
2016	3	A-003	Maxwell	2014-01-01	NULL	On Board	Accountant	300
2017	2	A-002	Adam	2013-01-01	NULL	On Board	HRM Assitant	200
2017	3	A-003	Maxwell	2014-01-01	NULL	On Board	Accountant	300

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

dharmend...

0 5

Reply
User: dharmendr
Replied: on Sep 28, 2017 05:24 AM
Report

Refer below code.

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 TOP 1 [Last_Designation] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]) order by [Promoted_Designation] desc)
        ELSE (SELECT TOP 1 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 TOP 1 [Last_Gross] FROM #Promotion WHERE [Prom_EmpNo]= e.emp_no AND DATEPART(YEAR,y.dt) >= DATEPART(YEAR,[WEF_Date]) order by [Promoted_Designation] desc)
        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

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