SQL：计算员工每天的使用时间

Question

SQL：我有一个在StartTime和EndTime列中包含员工工作时间的表。我想计算每个员工每天的工作时间，即使员工在一天开始轮班，第二天结束。

|Employee |StartTime         |EndTime         |
|A        | 01/01/2001 23:00 |02/01/2001 10:00|
|B        | 01/01/2001 21:00 |01/01/2001 22:00|

Output:
|Employee |Date         |HoursWorked              
|A        | 01/01/2001  | 1         |
|A        | 02/01/2001  | 10        |
|B        | 01/01/2001  | 1         |

Answer 1

您可以尝试以下查询-

create table #Employee (
     Employee varchar(10), StartTime smalldatetime      , EndTime smalldatetime
)
go

insert into #Employee   
select 'A'     , ' 01/01/2001 23:00', '02/01/2001 10:00' 
union all select 'B '     , '01/01/2001 21:00', '01/01/2001 22:00'   

select Employee ,  cast(StartTime as date) [Date] ,  datediff(hour,StartTime, EndTime)HoursWorked              
from #Employee 
where cast(StartTime as date) = cast(EndTime as date )
union all 
select Employee ,  cast(StartTime as date) [Date] ,    datediff(hour,StartTime, cast( concat(cast(StartTime as date) ,' 23:59:59' )as smalldatetime))  HoursWorked                  
from #Employee 
where cast(StartTime as date) <> cast(EndTime as date ) 
union all 
select Employee ,  cast(EndTime as date) [Date] ,    datediff(hour, cast( concat(cast(EndTime as date) ,' 00:00:00' )as smalldatetime) ,EndTime) HoursWorked                  
from #Employee 
where cast(StartTime as date) <> cast(EndTime as date ) 

Answer 2

使用DATEDIFF函数

create table #employee (Employee varchar(10), StartTime datetime, EndTime datetime)
insert into #employee values ('A','2001-01-01 23:00', '2001-01-02 10:00')

select Employee, CAST(StartTime as DATE) [Date], DATEDIFF(HOUR,StartTime, EndTime)HoursWorked from #employee

Answer 3

这里是一种使用递归CTE的方法：

DECLARE @t TABLE(
Employee NVARCHAR(10)
,StartTime DATETIME
,EndTime DATETIME
)

INSERT INTO @t VALUES ('A', '2001-01-01 23:00:00', '2001-01-03 10:00:00')
                     ,('A', '2001-01-05 21:00:00', '2001-01-06 22:00:00')
                     ,('A', '2001-01-07 21:00:00', '2001-01-08 22:00:00')
                     ,('B', '2001-01-01 21:00:00', '2001-01-01 22:00:00')
                     ,('B', '2001-01-02 21:00:00', '2001-01-03 02:00:00')
                     ,('C', '2001-01-03 02:00:00', '2001-01-04 00:00:00');

WITH cte AS(
  SELECT 1 AS lvl, Employee, CONVERT(DATE, StartTime) StartTime_DATE, StartTime, EndTime
    FROM @t AS t
  UNION ALL
  SELECT lvl + 1 AS lvl, c.employee, DATEADD(d, 1, c.StartTime_DATE) StartTime_DATE, c.StartTime, c.EndTime
    FROM cte AS c
    WHERE DATEADD(d, 1, c.StartTime_DATE) < c.EndTime
),
cteCalc AS(
  SELECT *
        ,CONVERT(DATE, StartTime) AS StartDate
        ,CASE WHEN lvl > 1 THEN CONVERT(DATETIME,CONVERT(DATE, DATEADD(d, DATEDIFF(d, StartTime, StartTime_DATE), StartTime))) ELSE DATEADD(d, DATEDIFF(d, StartTime, StartTime_DATE), StartTime) END AS StartTimeNew
        ,ISNULL(CONVERT(datetime, LEAD(StartTime_DATE) OVER (PARTITION BY Employee, CONVERT(DATE, StartTime) ORDER BY StartTime_DATE)), EndTime) AS EndTimeNew
    FROM cte

)
SELECT Employee, StartTime_DATE AS StartDate, DATEDIFF(MINUTE, StartTimeNew, EndTimeNew)/60.0 AS WorkHours
  FROM cteCalc
  ORDER BY Employee, StartTime_DATE
  OPTION (MAXRECURSION 0)