Sql脚本休假日期计算

Question

我有一个表employeeLeaveDetails

2022年9月5日2022年9月7日

AppNo
1
2	A
3	A
4

请尝试以下查询。

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)

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

LeaveTo

EmpCode	LeaveFrom
A	2022-09-01	2022.09-03
A	2022-09-05	2022年9月08日
A	2022-09-12	2022-09-15