How to get the employee YearWise and get the other details f

Basit Khan

9y1.1k

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Kiran Chanduri9y ago
Hi Basith Khan,

Modified the Query by adding required conditions and joins for Abrar's Answer . I think it gives you complete result even on future data modifications.
;WITH years
AS (SELECT 2013 AS year
UNION ALL
SELECT yl.year + 1 AS Year
FROM years yl
WHERE yl.year + 1 <= YEAR(GETDATE())
)

SELECT
Y.Year
,E.Emp_No
,E.Emp_Number
,E.Emp_Name
,E.Emp_JoiningDate
,Emp_Resign=ISNULL(E.Emp_ResignDate,NULL)
,E.Emp_status
,COALESCE(P.Last_Designation,E.Emp_Designation) AS Designation
,COALESCE(P.Last_Gross,E.Emp_Gross)

FROM years y
INNER JOIN Employee E ON (
(YEAR(E.Emp_JoiningDate) <= y.Year)
and
YEAR(ISNULL(E.Emp_ResignDate, GETDATE())) >= y.year
)
Left Outer JOIn promotion P ON
E.Emp_No=P.Prom_EmpNo AND y.Year <YEAR(P.WEF_Date)

ORDER BY year ASC;
0
Khan Abrar Ahmed9y ago
Hi try to modify select as per you requirement
DECLARE @Employee AS TABLE
(
[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
);
INSERT INTO @Employee
(
[Emp_No],
[Emp_Number],
[Emp_Name],
[Emp_JoiningDate],
[Emp_ResignDate],
[Emp_Status]
)
VALUES
(1, 'A-001', 'Alex', '2013-01-01', '2013-08-24', 'Resigned');
INSERT INTO @Employee
(
[Emp_No],
[Emp_Number],
[Emp_Name],
[Emp_JoiningDate],
[Emp_ResignDate],
[Emp_Status]
)
VALUES
(2, 'A-002', 'Adam', '2013-01-01', NULL, 'On Board');
INSERT INTO @Employee
(
[Emp_No],
[Emp_Number],
[Emp_Name],
[Emp_JoiningDate],
[Emp_ResignDate],
[Emp_Status]
)
VALUES
(3, 'A-003', 'Maxwell', '2014-01-01', NULL, 'On Board');
INSERT INTO @Employee
(
[Emp_No],
[Emp_Number],
[Emp_Name],
[Emp_JoiningDate],
[Emp_ResignDate],
[Emp_Status]
)
VALUES
(4, 'A-004', 'Smith', '2014-01-01', '2014-08-20', 'Resigned');
WITH years
AS (SELECT 2013 AS year
UNION ALL
SELECT yl.year + 1 AS year
FROM years yl
WHERE yl.year + 1 <= YEAR(GETDATE())
)
SELECT *
FROM years y
INNER JOIN @Employee ON (
(YEAR(Emp_JoiningDate) <= y.Year)
and
YEAR(ISNULL(Emp_ResignDate, GETDATE())) >= y.year
)
ORDER BY year ASC;
0