在sql中获取总时间 [英] getting total time in sql

问题描述

我在以h：m：s格式计算总工作时间时遇到问题



请帮助。



这是我的样本数据

I have problem with calculating the total working time in h:m:s format

pls help.

this is my sample data

empid time        date         status
1     9:00:04     08-01-2013   login
1     9:30:45     08-01-2013   logOut
1     9:45:20     08-01-2013   login
1     12:30:59    08-01-2013   logOut

推荐答案

你好Manoj



根据我的理解你想要这个.....



我创建一个像你提到的表（添加一个主键）并填写相同的数据.....

Hi Manoj

As per my understanding you want this .....

I create a table like you mention(add a primary key ) and fill the same data .....

Create table T1 
(ID int identity (1,1),
 EmpID int , 
 Etime time, 
 EDate date, 
 EStatus Char(1) )

写一个sql查询

Write a sql query

SELECT Tab1.EMPID , Tab1.EDate
,SUM([SECONDS]) AS [TOTAL TIME in SECONDS]
,CONVERT(varchar (20), SUM(Tab1.[SECONDS])/(60 * 60))+ ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60))/ 60) + ':' +
CONVERT(varchar(20), (SUM(Tab1.[SECONDS])%(60 * 60*60))%60) AS [WORKING TIME]
FROM
(SELECT A.EmpID , A.Etime AS [IN TIME]
, B.Etime AS [OUT TIME]
, A.EDate
, Datediff (SECOND,A.Etime,B.Etime  ) as [SECONDS]
FROM
T1 as A (NOLOCK)
INNER JOIN t1 as B  ON a.ID = (b.ID -1)  AND A.EStatus = 'I'
  WHERE  B.EStatus = 'O' ) AS Tab1
  GROUP BY Tab1.EMPID , Tab1.EDate

输出

Output

EMPID       EDate      TOTAL TIME in SECONDS WORKING TIME
----------- ---------- --------------------- --------------------------------------
1           2013-08-01  11780                 3:16:20


----------------------------------------------------------------------------------

问候，

Vijay

Regards,
Vijay

您尝试过什么？ DateDiff将为您提供两个日期之间的区别。

What have you tried ? DateDiff will give you the difference between two dates.

您可以参考版本2，在下面的链接中



使用sql server获取两个日期之间的持续时间，包括年，月，日和小时 [ ^ ]



快乐编码！

：）

you can refer version 2, in below link

Get duration between two dates in years,months,days and hours using sql server[^]

Happy Coding!
:)

这篇关于在sql中获取总时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！