How to get the Employee Year Wise Depend upon Joining Resigning and Status.
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.