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

Hi,

How to get the Employee Year Wise Depend upon Joining Resigning and Status.
The 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
) ON [PRIMARY]

GO

-- below is the data

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

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.

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
      ,stuff((select ','+e.emp_number
                from employee as e
               where e.emp_joiningdate<=y.dt
                 and isnull(e.emp_resigndate,current_timestamp)>=y.dt
               order by e.emp_number
                 for xml path('')
              )
              ,1
              ,1
              ,''
            )
       as emp_list
  from cte_dt as y
;

Many Many Thanks
O/P is showing Correctly.
sorry i want o/p in tabular format.

image

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
;

Many Many Thanks
Basit

How can i use this one in SQL View?

Thanks
Basit.

I used procedure.

Many Thanks
Basit

can you please check below link.

Thanks alot bitsmed,

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

INSERT INTO [Employee]
           ([Emp_No]
           ,[Emp_Number]
           ,[Emp_Name]
           ,[Emp_JoiningDate]
           ,[Emp_ResignDate]
           ,[Emp_Status])
     VALUES(
           1
           ,'A-001'
           ,'Alex'
           ,'2013-07-01'
           ,null
           ,'On Board')
GO

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
;

with above query Year-2013 is not showing for Alex, Joining date is 01-07-2013, year should start from 2013 but start from 2014.

Thanks
Basit.

Change this:

               on e.emp_joiningdate<=y.dt

to this:

               on e.emp_joiningdate<dateadd(year,1,y.dt)

Complete:

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<dateadd(year,1,y.dt)
              and isnull(e.emp_resigndate,current_timestamp)>=y.dt
;

Many Many Thanks.