现有如下表
ID date On
----------------------------
A 2014-10-1 1
A 2014-10-2 0
A 2014-10-3 1
A 2014-10-4 1
A 2014-10-5 1
A 2014-10-6 1
B 2014-10-1 1
B 2014-10-2 1
B 2014-10-3 1
B 2014-10-4 0
B 2014-10-5 0
B 2014-10-6 1
C 2014-10-1 1
C 2014-10-2 1
C 2014-10-3 1
C 2014-10-4 1
C 2014-10-5 1
C 2014-10-6 1
现在统计某员工连续上班的天数或都连续上班大于等于4天的记录，谢谢，希望记录如下：
ID StartDate EndDate Count
A 2014-10-2 2014-10-6 4
C 2014-10-1 2014-10-6 6
解答即给分，各位大侠请帮忙，谢谢。
------解决思路----------------------
CREATE TABLE #temp (id CHAR(1),date datetime,[on] BIT)
INSERT #temp
SELECT 'A','2014-10-1',1 UNION ALL
SELECT 'A','2014-10-2',0 UNION ALL
SELECT 'A','2014-10-3',1 UNION ALL
SELECT 'A','2014-10-4',1 UNION ALL
SELECT 'A','2014-10-5',1 UNION ALL
SELECT 'A','2014-10-6',1 UNION ALL
SELECT 'B','2014-10-1',1 UNION ALL
SELECT 'B','2014-10-2',1 UNION ALL
SELECT 'B','2014-10-3',1 UNION ALL
SELECT 'B','2014-10-4',0 UNION ALL
SELECT 'B','2014-10-5',0 UNION ALL
SELECT 'B','2014-10-6',1 UNION ALL
SELECT 'C','2014-10-1',1 UNION ALL
SELECT 'C','2014-10-2',1 UNION ALL
SELECT 'C','2014-10-3',1 UNION ALL
SELECT 'C','2014-10-4',1 UNION ALL
SELECT 'C','2014-10-5',1 UNION ALL
SELECT 'C','2014-10-6',1
;WITH a1 AS
(
SELECT *,date-ROW_NUMBER() OVER(PARTITION BY id ORDER BY date) re
FROM #temp
WHERE [on]=1
)
SELECT ID,MIN(date) StartDate,MAX(date) EndDate,COUNT(*) [count]
FROM a1
GROUP BY ID,re
HAVING COUNT(*)>=4
ORDER BY ID
------解决思路----------------------
WITH table1(ID,date,[On]) AS (
SELECT 'A',Convert(datetime,'2014-10-1',120),1 UNION ALL
SELECT 'A',Convert(datetime,'2014-10-2',120),0 UNION ALL
SELECT 'A',Convert(datetime,'2014-10-3',120),1 UNION ALL
SELECT 'A',Convert(datetime,'2014-10-4',120),1 UNION ALL
SELECT 'A',Convert(datetime,'2014-10-5',120),1 UNION ALL
SELECT 'A',Convert(datetime,'2014-10-6',120),1 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-1',120),1 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-2',120),1 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-3',120),1 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-4',120),0 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-5',120),0 UNION ALL
SELECT 'B',Convert(datetime,'2014-10-6',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-1',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-2',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-3',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-4',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-5',120),1 UNION ALL
SELECT 'C',Convert(datetime,'2014-10-6',120),1
)
,t1 AS (
SELECT ID,date, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date) rn
FROM table1
WHERE [on]=1
AND NOT EXISTS (SELECT *
FROM table1 t
WHERE t.ID = table1.ID
AND t.[on] = 1
AND DateDiff(day,t.date,table1.date) = 1
)
)
,t2 AS (
SELECT ID,date, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date) rn
FROM table1
WHERE [on]=1
AND NOT EXISTS (SELECT *
FROM table1 t
WHERE t.ID = table1.ID
AND t.[on] = 1
AND DateDiff(day,t.date,table1.date) = -1
)
)
SELECT t1.ID,
t1.date StartDate,
t2.date EndDate,
DateDiff(day,t1.date,t2.date)+1 [Count]
FROM t1
JOIN t2
ON t1.ID = t2.ID
AND t1.rn = t2.rn
WHERE DateDiff(day,t1.date,t2.date)+1 >= 4
ID StartDate EndDate Count
---- ----------------------- ----------------------- -----------
A 2014-10-03 00:00:00.000 2014-10-06 00:00:00.000 4
C 2014-10-01 00:00:00.000 2014-10-06 00:00:00.000 6
------解决思路----------------------
加上条件WHERE [On] =1
SELECT [ID],MIN([date]) AS StartDate,MAX([date]) AS EndDate,COUNT(1) AS DayCount
FROM (SELECT [ID]
,[date]
,[On]
,DATEADD(dd,-ROW_NUMBER() OVER ( PARTITION BY [ID] ORDER BY DATEADD(yy,-1000*CAST([On] AS INT),[date])),[date]) AS Grp
FROM #T1
) AS T
WHERE [On] =1
GROUP BY [ID],[Grp]
HAVING COUNT(1)>=4
ORDER BY 1