CREATE TABLE [dbo].[EmpLeave](
[EmpNo] [nvarchar](50) NULL,
[LeaveDate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO EmpLeave VALUES('F01','2007-10-2 00:00')
INSERT INTO EmpLeave VALUES('F01','2007-10-3 00:00')
INSERT INTO EmpLeave VALUES('F04','2007-10-8 00:00')
INSERT INTO EmpLeave VALUES('F04','2007-10-9 00:00')
INSERT INTO EmpLeave VALUES('F03','2007-10-6 00:00')
INSERT INTO EmpLeave VALUES('f01','2007-10-10 00:00')
SELECT EmpNo,min(LeaveDate) as StartDate,max(LeaveDate) as EndDate
FROM
(
select a.*,ROUND(CAST(ROW_NUMBER() OVER (order by EmpNo,LeaveDate) AS DECIMAL(18,2))/2,0) AS GP
from
(
select EmpNo=isnull(A.EmpNo,B.EmpNo)
,LeaveDate=isnull(A.LeaveDate,B.LeaveDate)
from dbo.EmpLeave A full outer join dbo.EmpLeave B on A.EmpNo=B.EmpNo
and A.LeaveDate=dateadd(d,1,B.LeaveDate)
WHERE A.LeaveDate is null or B.LeaveDate is null
) AS a
) AS b
GROUP BY EmpNo,GP