从请假日期列表中取得请假起止日期

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

转载于:https://www.cnblogs.com/brawei/archive/2007/11/12/957364.html