需要帮助找到正确的T-SQL查询 [英] Need help finding the correct T-SQL Query

问题描述

我不太确定该怎么做.基本上我有一个像这样的表

I am not quite sure how to go about doing this. Basically I have have a table like this

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

基本上包含雇员要求放假的日子.现在，当授予一天或几天后，需要将这些数据复制到表格的表格中

Which basically contains the days an employee requests a holiday. Now, when a day or days is granted, this data needs to be copied over to a table of the form

UserId DateFrom DateTo

所以基本上对于我想要的上述数据:

So basically for the above data i want:

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，所以我宁愿使用非迭代解决方案.

I.e I want consecutive days in the DateFrom and DateTo. Now I am not sure how to do this without using a while loop. This is SQL, So i would prefer a non-iterative solution.

请告知！！！

推荐答案

;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

这篇关于需要帮助找到正确的T-SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！