select Name,leaveTime,shouldLeave,(case when leaveTime is null then '未休假' when shouldLeave>leaveTime then '提前休假' when shouldLeave<leaveTime then '延迟休假' else '正常休假' end ) from
(select Name,t1.leaveTime ,
dateadd(month,6, --增加六个月
(select top 1 leaveTime from Leave where leave.leaveTime<t1.leaveTime and Leave.Pid=Person.Id order by leaveTime desc )) as shouldLeave--获取上次休假时间
from Person left join (select Pid, Leave.leaveTime from Leave where Leave.leaveTime between '2013-01-01' and '2014-12-31')as t1
on Person.Id=t1.Pid )as t3
(select Name,t1.leaveTime ,
dateadd(month,6, --增加六个月
(select top 1 leaveTime from Leave where leave.leaveTime<t1.leaveTime and Leave.Pid=Person.Id order by leaveTime desc )) as shouldLeave--获取上次休假时间
from Person left join (select Pid, Leave.leaveTime from Leave where Leave.leaveTime between '2013-01-01' and '2014-12-31')as t1
on Person.Id=t1.Pid )as t3