我不太清楚该怎么做.基本上我有一张这样的桌子
UserId DateRequested Approved ApprovedBy Notes ------------ ----------------------- -------- ----------- ----- 1 2011-05-26 0 NULL NULL 1 2011-05-27 0 NULL NULL 1 2011-05-28 0 NULL NULL 1 2011-06-05 0 NULL NULL 1 2011-06-06 0 NULL NULL 1 2011-06-25 0 NULL NULL
这基本上包含员工请求假期的日子.现在,当授予一天或几天时,需要将此数据复制到表单的表中
UserId DateFrom DateTo
所以基本上对于我想要的上述数据：
UserId DateFrom DateTo ------------------------------- 1 2011-05-26 2011-05-28 1 2011-06-05 2011-06-06 1 2011-06-25 2011-06-25
我想在DateFrom和DateTo中连续几天.现在我不知道如何在不使用while循环的情况下执行此操作.这是sql,所以我更喜欢非迭代解决方案.
请指教！！！
解决方法
;WITH cte AS
(
SELECT *,DATEDIFF(DAY,DateRequested)-
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY DateRequested) AS Grp
FROM YourTable
WHERE Approved = 1 /*Presumably - but your example data doesn't show this.*/
)
SELECT UserId,MIN(DateRequested) AS DateFrom,MAX(DateRequested) AS DateTo
FROM cte
GROUP BY UserId,Grp
