How to get the Employee Year Wise Depend upon Joining Resign

Basit Khan

9y1.1kSQL

Hi,

How to get the Employee Year Wise Depend upon Joining Resigning and Status.

Below is table

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

) ON [PRIMARY]

DATA IS BELOW.

INSERT INTO [Employee]

([Emp_No]

,[Emp_Number]

,[Emp_Name]

,[Emp_JoiningDate]

,[Emp_ResignDate]

,[Emp_Status])

VALUES(

,'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(

,'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(

,'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(

,'A-004'

,'Smith'

,'2014-01-01'

,'2014-08-20'

,'Resigned')

The O/P is

2013- A-001,A-002

2014-A-002,A-003,A-004

2015-A-002,A-003

2016-A-002,A-003

2017-A-002,A-003

2018-A-002,A-003

Employee A-002 & A-003 will show Until they Resigned.

Thanks

Basit.

4 Replies

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

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;

Basit Khan9y ago

Many Many Thanks for your help.

for this O/P i want to only show you that's the employee number i want to show. sorry i want the o/p in tabular format.

below is the actual o/p

Year	Emp_No	Emp_Number	Emp_Name	Emp_JoiningDate	Emp_ResignDate	Emp_Status
2013	1	A-001	Alex	1/1/2013	8/24/2013	Resigned
2013	2	A-002	Adam	1/1/2013	NULL	On Board
2014	2	A-002	Adam	1/1/2013	NULL	On Board
2014	3	A-003	Maxwell	1/1/2014	NULL	On Board
2014	4	A-004	Smith	1/1/2014	8/20/2014	Resigned
2015	2	A-002	Adam	1/1/2013	NULL	On Board
2015	3	A-003	Maxwell	1/1/2014	NULL	On Board
2016	2	A-002	Adam	1/1/2013	NULL	On Board
2016	3	A-003	Maxwell	1/1/2014	NULL	On Board
2017	2	A-002	Adam	1/1/2013	NULL	On Board
2017	3	A-003	Maxwell	1/1/2014	NULL	On Board

Thanks once again

Basit.

Khan Abrar Ahmed9y ago
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 year,
(
SELECT CAST(Emp_Number AS VARCHAR) + ','
FROM @Employee
WHERE (
(YEAR(Emp_JoiningDate) <= y.Year)
and
YEAR(ISNULL(Emp_ResignDate, GETDATE())) >= y.year
)
FOR XML PATH('')
)
FROM years y
ORDER BY year ASC;
SELECT *
FROM @Employee;
Sundaram Subramanian9y ago
1. select * from Employee where Emp_Status='On Board'
Hope this is for you are looking for. If not, let me know in clear about your requierment.
Thanks in advance