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

[basit0079]

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.

[bitsmed]

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
;

[basit0079]

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.

Thanks
Basit

[bitsmed]

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
;

[basit0079]

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

[basit0079]

How can i use this one in SQL View?

Thanks
Basit.

[basit0079]

I used procedure.

Many Thanks
Basit

[basit0079]

can you please check below link.

How to get the employee YearWise and get the other details from Promotion table accordingly

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] …

[basit0079]

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.

[bitsmed]

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
;

[basit0079]

Many Many Thanks.