代码之家 › 专栏 › 技术社区 › Doonie Darkoo

总时间不正确

sql-server

-1

Doonie Darkoo · 技术社区 · 6 年前

我有一张包含员工出勤情况的桌子。我想计算员工每个月花费的总时间。我能计算出每天所花的时间,但这并不是在增加时间,而是提供一个整体。

以下是与shema的链接: https://liveweave.com/q0iBdb

下面是一个查询,它计算每个员工每天花费的时间。

SELECT
CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([AttendanceTimeIn]) AS TIME), CAST(MAX([AttendanceTimeOut]) AS TIME))/3600) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([AttendanceTimeIn]) AS TIME), CAST(MAX([AttendanceTimeOut]) AS TIME)) % 3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([AttendanceTimeIn]) AS TIME), CAST(MAX([AttendanceTimeOut]) AS TIME)) % 60) , 2 ) 

       AS HoursSpent

FROM 
[HRM].[tbl_EmployeeAttendance] [Attendance]
WHERE 
[Attendance].[RecordStatusCode] != '13' AND [Attendance].[EmpCode] = 22
AND
CAST(GETDATE()-5 AS DATE) = CAST(ISNULL([AttendanceTimeIn], [AttendanceTimeOut]) AS Date)

这是我到目前为止试过的

SELECT CONVERT(varchar(10), SUM(DATEDIFF(MINUTE, CAST(AttendanceTimeIn AS TIME), CAST(AttendanceTimeOut AS TIME))) / 60) + ':' + CONVERT(varchar(10), SUM(DATEDIFF(MINUTE, CAST(AttendanceTimeIn AS TIME), CAST(AttendanceTimeOut AS TIME))) % 60)
FROM [HRM].[tbl_EmployeeAttendance]
WHERE CAST(COALESCE(AttendanceTimeIn, AttendanceTimeOut) AS DATE) BETWEEN '2018-08-01' AND GETDATE()  
AND
RecordStatusCode != '13' AND EmpCode = 22

1 回复 | 直到 6 年前

LukStorms 6 年前

时差的计算可以分为总小时数和剩余的分钟和秒数。

示例代码段:

-- Using a table variable for demonstration purposes
declare @EmployeeAttendance table (ID int identity(1,1) primary key, EmpCode int, AttendanceTimeIn datetime, AttendanceTimeOut datetime);
-- Simplified sample data
insert into @EmployeeAttendance (EmpCode, AttendanceTimeIn, AttendanceTimeOut) values 
 (11,'2018-08-01 09:20:00', null)
,(11,null, '2018-08-01 21:35:15')
,(12,'2018-08-01 09:00:00', null)
,(12,null, '2018-08-01 22:20:20')
,(13,'2018-08-01 09:00:00',null)
,(13,'2018-08-01 12:00:00',null)
;

-- The query
WITH EMPLOYEEATTENDANCE AS
(
   select 
   EmpCode, 
   cast(coalesce(AttendanceTimeIn, AttendanceTimeOut) as date) as AttendanceDate, 
   cast(min(AttendanceTimeIn) as time) as MinEmpTimeIn,
   cast(max(AttendanceTimeIn) as time) as MaxEmpTimeIn,
   cast(max(AttendanceTimeOut) as time) as MaxEmpTimeOut
   --,max(cast(max(AttendanceTimeOut) as time)) over (partition by cast(coalesce(AttendanceTimeIn, AttendanceTimeOut) as date)) as MaxDateTimeOut
   from @EmployeeAttendance
   where cast(coalesce(AttendanceTimeIn, AttendanceTimeOut) as date) between cast('2018-08-01' as date) and cast('2018-08-01' as date)
   group by EmpCode, cast(coalesce(AttendanceTimeIn, AttendanceTimeOut) as date)
)
SELECT 
 CONCAT(SUM(DATEDIFF(HOUR,MinEmpTimeIn, coalesce(MaxEmpTimeOut,MaxEmpTimeIn))), 
        SUBSTRING(CONVERT(varchar(30), 
                  DATEADD(ms,(SUM(DATEDIFF(SECOND,MinEmpTimeIn, coalesce(MaxEmpTimeOut,MaxEmpTimeIn)))%3600)*1000,0)
                  ,114),3,6)) as TotalTimeDiff
FROM EMPLOYEEATTENDANCE;

TotalTimeDiff
-------------
28:35:35

但在你的样本数据中,只有1个EmpCode的1个AttendanceTimeOut。

如果数据不完整,则预期结果不完整。