SQL统计连续加班天数的问题

Question

连续加班统计

Answer 1

select  id, count(date) from 表
where on = 1
group by id
having count(date) >=4

Answer 2

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

Answer 3

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

Answer 4

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([ID] nvarchar(1),[date] DATE,[On] BIT)
Insert #T1
select N'A','2014-10-1',1 union all
select N'A','2014-10-2',0 union all
select N'A','2014-10-3',1 union all
select N'A','2014-10-4',1 union all
select N'A','2014-10-5',1 union all
select N'A','2014-10-6',1 union all
select N'B','2014-10-1',1 union all
select N'B','2014-10-2',1 union all
select N'B','2014-10-3',1 union all
select N'B','2014-10-4',0 union all
select N'B','2014-10-5',0 union all
select N'B','2014-10-6',1 union all
select N'C','2014-10-1',1 union all
select N'C','2014-10-2',1 union all
select N'C','2014-10-3',1 union all
select N'C','2014-10-4',1 union all
select N'C','2014-10-5',1 union all
select N'C','2014-10-6',1
GoSELECT [ID],MIN([date]) AS StartDate,MAX([date]) AS EndDate,COUNT(1) AS DayCount
FROM (SELECT  [ID]
       ,[date]
       ,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
GROUP BY [ID],[Grp]
HAVING COUNT(1)>=4
ORDER BY 1
/*
ID StartDate EndDate DayCount
A 2014-10-03 2014-10-06 4
C 2014-10-01 2014-10-06 6
*/

Answer 5

加上条件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