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
