SQLTeam.com | Weblogs | Forums

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

Transact-SQL
#1

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.

#2 
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
;
#3

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

image

Thanks
Basit

#4 
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
;
#5

Many Many Thanks
Basit

#6

How can i use this one in SQL View?

Thanks
Basit.

#7

I used procedure.

Many Thanks
Basit

#8

can you please check below link.

#9

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.

#10

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
;
#11

Many Many Thanks.