Это проблема Gaps and Islands . Самый простой способ решить это - использовать ROW_NUMBER()
для определения пробелов в последовательности:
SELECT UserName,
UserDate,
UserCode,
GroupingSet = DATEADD(DAY,
-ROW_NUMBER() OVER(PARTITION BY UserName
ORDER BY UserDate),
UserDate)
FROM UserTable;
Это дает:
UserName | UserDate | UserCode | GroupingSet
------------+---------------+------------+-------------
user1 | 09-01-2014 | 1 | 08-31-2014
user1 | 09-02-2014 | 0 | 08-31-2014
user1 | 09-03-2014 | 1 | 08-31-2014
user1 | 09-08-2014 | 1 | 09-04-2014
user1 | 09-09-2014 | 0 | 09-04-2014
user1 | 09-10-2014 | 1 | 09-04-2014
user1 | 09-11-2014 | 1 | 09-04-2014
user2 | 09-01-2014 | 1 | 08-31-2014
user2 | 09-04-2014 | 1 | 09-02-2014
user2 | 09-05-2014 | 1 | 09-02-2014
user2 | 09-06-2014 | 0 | 09-02-2014
user2 | 09-07-2014 | 1 | 09-02-2014
Как вы можете видеть, это дает постоянную значение в GroupingSet
для последовательных строк. Затем вы можете группировать этот код для получения требуемого свода:
WITH CTE AS
( SELECT UserName,
UserDate,
UserCode,
GroupingSet = DATEADD(DAY,
-ROW_NUMBER() OVER(PARTITION BY UserName
ORDER BY UserDate),
UserDate)
FROM UserTable
)
SELECT UserName,
StartDate = MIN(UserDate),
EndDate = MAX(UserDate),
Result = COUNT(NULLIF(UserCode, 0))
FROM CTE
GROUP BY UserName, GroupingSet
HAVING COUNT(NULLIF(UserCode, 0)) > 1
ORDER BY UserName, StartDate;