我不太确定该怎么做。基本上我有一张这样的桌子
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,0,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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)