How to get the Employee Year Wise Depend upon Joining Resign

Question

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]

GO

 

DATA IS BELOW.

 

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')

GO

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')

GO

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')

GO

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')

GO

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.