下文将产生预期产出,但我不敢肯定,如果你不做更好的勾销,这就是C#Q。

Update This has been updated to a proper gaps and island solution. This is based on the MSDN article Islands and Gaps in Sequential Numbers by Alexander Kozak. This could be improved on by using CTE s and also the Exists could be replaced with LEFT JOINs.

应当指出,这取决于日期是否没有任何时间组成部分。 如果有时间组成部分,就必须先撤除。

产出

Date                    enddate                 FirstName LastName Unit
----------------------- ----------------------- --------- -------- -------
2011-01-01 00:00:00.000 2011-01-03 00:00:00.000 Bob       Guy      Home
2011-01-03 00:00:00.000 2011-01-05 00:00:00.000 Jane      Doe      Home
2011-01-05 00:00:00.000 2011-01-07 00:00:00.000 Bob       Guy      Home
2011-01-08 00:00:00.000 2011-01-08 00:00:00.000 Bob       Guy      Offsite
2011-01-09 00:00:00.000 2011-01-11 00:00:00.000 Bob       Guy      Home
2011-01-12 00:00:00.000 2011-01-14 00:00:00.000 Jane      Doe      Home

声明

SET NOCOUNT On

DECLARE @Test 
Table (
    Date datetime, 
    FirstName varchar(100),
     LastName varchar(100), 
     Unit  varchar(7))



INSERT INTO @Test VALUES ( 01/01/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/02/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/03/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/05/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/06/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/07/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/08/2011   ,  Bob ,  Guy ,   Offsite )
INSERT INTO @Test VALUES ( 01/03/2011   ,  Jane ,  Doe ,   Home )
INSERT INTO @Test VALUES ( 01/04/2011   ,  Jane ,  Doe ,   Home )
INSERT INTO @Test VALUES ( 01/05/2011   ,  Jane ,  Doe ,   Home )
INSERT INTO @Test VALUES ( 01/09/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/10/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/11/2011   ,  Bob ,  Guy ,   Home )
INSERT INTO @Test VALUES ( 01/12/2011   ,  Jane ,  Doe ,   Home )
INSERT INTO @Test VALUES ( 01/13/2011   ,  Jane ,  Doe ,   Home )
INSERT INTO @Test VALUES ( 01/14/2011   ,  Jane ,  Doe ,   Home )


SELECT 
    t1.Date,
    MIN(t2.Date) enddate, 
    t1.FirstName,
    t1.LastName,
    t1.Unit
 FROM

    (SELECT * 
    FROM
        @Test t1
    WHERE
       NOT EXISTS(SELECT * FROM @Test t2
                  WHERE
                      t1.firstName = t2.FirstName   
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                        and t1.Date - t2.Date = 1)) 
        t1


      INNER JOIN (SELECT * FROM @Test t1

    WHERE
       NOT EXISTS(SELECT * FROM @Test t2
                  WHERE
                      t1.firstName = t2.FirstName   
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                        and t2.Date - t1.Date = 1)) t2
      ON
        t1.firstName = t2.FirstName 
                      AND t1.LastName = t2.LastName
                      AND t1.Unit = t2.Unit
                      AND t1.Date <= t2.Date  
       GROUP BY
       t1.Date,
       t1.FirstName,
       t1.LastName,
       t1.Unit

采用康拉德测试数据和Izik Ben Gans方法!

;WITH base AS ( 
SELECT    FirstName, 
          LastName, 
          Unit,
          Date,
          DATEDIFF(DAY,0,Date) - 
                         DENSE_RANK() OVER (PARTITION BY FirstName, LastName, Unit 
                                            ORDER BY DATEDIFF(DAY,0,Date)) AS G
 FROM     @Test
 )
SELECT FirstName, LastName, Unit, MIN(Date) DateStart,MAX(Date) DateEnd
FROM base
GROUP BY G, FirstName, LastName, Unit