I have a table employeeLeaveDetails

If an Employee took leave from 01/09/2022 to 03/09/2022, after they comes back, taken another leave from 05/09/2022 to 06/09/2022, instead of come back on duty on 07/09/2022, they've extended leave for 2 days. means, this leave from 05/09/2022 - 08/09/2022

Result I am looking like below

Application No: 2 & 3 Extended Leave ( Leave to 1 day = next leave from)

CodePudding user response：

Please try the following query.

SELECT 
E1.empcode,
E1.LeaveFrom,
ISNULL(E2.LeaveTo,E1.LeaveTo) AS LeaveTo
FROM employeeLeaveDetails E1 LEFT OUTER JOIN
employeeLeaveDetails E2 ON DATEADD(DAY,1,E1.LeaveTo)=E2.LeaveFrom AND E1.empcode=E2.empcode
WHERE E1.AppNo(PK) NOT IN (SELECT  ISNULL(EE2.AppNo(PK),'') FROM employeeLeaveDetails EE1 LEFT OUTER JOIN
employeeLeaveDetails EE2 ON DATEADD(DAY,1,EE1.LeaveTo)=EE2.LeaveFrom AND E1.empcode=E2.empcode)

CodePudding user response：

select   EmpCode
        ,min(LeaveFrom) as LeaveFrom
        ,LeaveTo
from     (
         select EmpCode
               ,LeaveFrom
               ,case when datediff(day, LeaveTo, lead(LeaveFrom) over(partition by EmpCode order by LeaveFrom)) <= 1 then lead(LeaveTo) over(partition by EmpCode order by LeaveFrom) else LeaveTo end as LeaveTo
         from   t
         ) t
group by EmpCode, LeaveTo

Fiddle