时间戳

首页 > 解决方案 > 我怎样才能总结所有的加班时间

sql - 我怎样才能总结所有的加班时间

问题描述

我无法弄清楚如何从查询中总结所有持续时间。我必须使用另一个查询来总结它......

这是我为获取超时持续时间和其他持续时间所做的查询......

Select  empId, firstTimeIn, firstTimeOut, secondTimeIn, secondTimeOut, overTimeIn, overTimeOut, attendanceDate,

          (RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) / 3600 AS VARCHAR(2)), 2)  + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) % 3600 % 60 AS VARCHAR(2)), 2) ) as FirstTime
          ,                 
          (RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) / 3600 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) % 3600 % 60 AS VARCHAR(2)), 2)) as SecondTime
          ,                 
          (RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, overTimeIn), Convert (time, overTimeOut) ) / 3600 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, overTimeIn), Convert (time, overTimeOut) ) % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, overTimeIn), Convert (time, overTimeOut) ) % 3600 % 60 AS VARCHAR(2)), 2)) as OverTimeDuration
from tbl_EmployeeAttendance Where attendanceDate BETWEEN '2019-09-01' AND '2019-09-15' And empId = '41' AND
          ((RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) / 3600 AS VARCHAR(2)), 2)  + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, firstTimeIn), Convert (time, firstTimeOut) ) % 3600 % 60 AS VARCHAR(2)), 2) ) >= '04:00:00') AND
          ((RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) / 3600 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(DATEDIFF(second, Convert (time, secondTimeIn), Convert (time, secondTimeOut) ) % 3600 % 60 AS VARCHAR(2)), 2)) >= '04:00:00')

尝试使用此代码添加表及其值....

CREATE TABLE tbl_EmployeeAttendance  (
    empId int,
    firstTimeIn time,
    firstTimeOut time,
    secondTimeIn time,
    secondTimeOut time,
    overTimeIn time,
    overTimeOut time,
    attendanceDate varchar(100)
)

Insert into tbl_EmployeeAttendance VALUES ('41','07:30:00','12:10:00','12:55:00','17:20:00','17:45:00','19:30:02','2019-09-02')
Insert into tbl_EmployeeAttendance VALUES ('41','07:24:00','12:12:00','12:55:00','17:20:00','','','2019-09-03')
Insert into tbl_EmployeeAttendance VALUES ('41','07:23:00','12:10:00','12:55:00','17:20:00','','','2019-09-04')
Insert into tbl_EmployeeAttendance VALUES ('41','07:30:00','12:10:00','12:55:00','17:20:00','','','2019-09-05')
Insert into tbl_EmployeeAttendance VALUES ('41','07:24:00','12:12:00','12:55:00','17:20:00','','','2019-09-06')
Insert into tbl_EmployeeAttendance VALUES ('41','07:05:00','12:09:00','12:55:00','17:20:00','17:45:00','19:30:02','2019-09-09')
Insert into tbl_EmployeeAttendance VALUES ('41','07:05:00','12:09:00','12:55:00','17:20:00','','','2019-09-10')
Insert into tbl_EmployeeAttendance VALUES ('41','07:05:00','12:09:00','12:55:00','17:20:00','','','2019-09-11')
Insert into tbl_EmployeeAttendance VALUES ('41','07:30:00','12:09:00','12:55:00','17:20:00','','','2019-09-12')
Insert into tbl_EmployeeAttendance VALUES ('41','07:30:00','12:10:00','12:55:00','17:20:00','17:45:00','19:30:02','2019-09-13')

标签： sql

解决方案

使用DATEDIFF(interval,date1,date2)如果您使用MSSQL 

SELECT empId, convert(varchar(5),totalFirstTiomeInHoure/3600)+':'+convert(varchar(5),totalFirstTiomeInHoure%3600/60)+':'+convert(varchar(5),(totalFirstTiomeInHoure%60)) as FirstTime,
   convert(varchar(5),totalSecondTiomeInHoure/3600)+':'+convert(varchar(5),totalSecondTiomeInHoure%3600/60)+':'+convert(varchar(5),(totalSecondTiomeInHoure%60)) as SecondTime,
   convert(varchar(5),totalOverTiomeInHoure/3600)+':'+convert(varchar(5),totalOverTiomeInHoure%3600/60)+':'+convert(varchar(5),(totalOverTiomeInHoure%60)) as overTime
   From (    SELECT empId,
    Sum(Datediff(s,firstTimeIn,firstTimeOut)) as totalFirstTiomeInHoure,
  Sum(Datediff(s,secondTimeIn,secondTimeOut)) as totalSecondTiomeInHoure,
  Sum(Datediff(s,overTimeIn,overTimeOut )) as totalOverTiomeInHoure
 FROM [dbo].[tbl_EmployeeAttendance]
 WHERE attendanceDate BETWEEN '2019-09-01' AND '2019-09-15'
 GROUP BY [empId]) as TempData

推荐阅读