Calculate employee total time period in company from joining date to end date in SQL Server

Last Reply on Aug 23, 2017 07:04 AM By dharmendr

Posted on Aug 23, 2017 05:50 AM

How to Get Employees Period in Company as per Employee Joining Date

Example, I have a field
Emp_Number,    Emp_Name,  Emp_JoiningDate Period Start Period End
A-1                       Alex           2013-07-14    2017-07-14 2018-07-14
A-2                     Sachin         2017-08-14     2017-08-14 2018-08-14

Thanks

Basit.

 

Posted on Aug 23, 2017 07:04 AM Modified on on Sep 08, 2017 05:57 AM

Hi basit0079,

SQL

DECLARE @Employee AS TABLE(Emp_Number CHAR(5),Emp_Name VARCHAR(10),Emp_JoiningDate DATE,Period_Start DATE,Period_End DATE)
INSERT INTO @Employee VALUES('A-1','Alex','2013-07-14','2017-07-15','2017-10-15')
INSERT INTO @Employee VALUES('A-2','Sachin','2017-08-14','2017-08-14','2017-08-15')
INSERT INTO @Employee VALUES('A-3','Sachin','2017-08-14','2017-11-14','2019-12-30')

DECLARE @EmployeeTemp AS TABLE(Emp_Number CHAR(5),Emp_Name VARCHAR(10),Emp_JoiningDate DATE,Period_Start DATE,Period_End DATE)

insert into @EmployeeTemp
SELECT
	  Emp_Number
	  ,Emp_Name
	  ,Emp_JoiningDate
	  ,CONVERT(VARCHAR(4),DATEPART(YY,Period_Start)) + '-' + RIGHT('00'+CONVERT(VARCHAR(2),DATEPART(M,Period_Start)),2) +'-'+ '01' Period_Start
	  ,DATEADD(D,- DATEDIFF(DAY,DATEPART(d,CONVERT(VARCHAR(4),DATEPART(YY,Period_Start)) + '-' + RIGHT('00'+CONVERT(VARCHAR(2),DATEPART(M,Period_Start)),2) +'-'+ '01'),DATEPART(d,Period_Start)),Period_End) Period_End
FROM @Employee

SELECT  CASE WHEN DATEDIFF(MONTH,Period_Start,Period_End) < 12
			 THEN  ''
			 WHEN (DATEDIFF(MONTH,Period_Start,Period_End) % 12 = 0)
			 THEN  CONVERT(VARCHAR(4),DATEDIFF(MONTH,Period_Start,Period_End) / 12 ) + ' Years '			  
             ELSE  CONVERT(VARCHAR(4),DATEDIFF(MONTH,Period_Start,Period_End) / 12 ) + ' Years '
			 END
	   + CASE WHEN DATEDIFF(MONTH,Period_Start,Period_End) < 1
			  THEN ''
			  WHEN DATEDIFF(MONTH,Period_Start,Period_End) < 12
			  THEN CONVERT(VARCHAR(2),DATEDIFF(MONTH,Period_Start,Period_End)) + ' Months '
		      WHEN DATEDIFF(MONTH,Period_Start,Period_End) % 12 > 0
			  THEN CONVERT(VARCHAR(2),DATEDIFF(MONTH,Period_Start,Period_End) % 12) + ' Months '
			  ELSE ''
		      END
	   +CASE WHEN DATEDIFF(DAY,(DATEADD(MONTH,DATEDIFF(MONTH,Period_Start,Period_End),Period_Start)),Period_End) > 0
		     THEN CONVERT(VARCHAR(2),DATEDIFF(DAY,(DATEADD(MONTH,DATEDIFF(MONTH,Period_Start,Period_End),Period_Start)),Period_End)) + ' Days'
		     ELSE ''
		END
		AS [Working Period]
FROM @EmployeeTemp

Output

Working Period

3 Months

1 Days

2 Years 1 Months 16 Days