使用sql计算总工作时间(以分钟为单位) [英] Calculate total working hours with min using sql

问题描述

我在计算emp的总工作时间时遇到问题.员工可以在一天之内多次注销..
我的问题是我如何计算每个员工的工作时间.

这是我的表格数据....

EmpId日期登录/注销状态

E001 02/10/2012 09:00:00 AM 1
E002 02/10/2012 09:00:00 AM 1
E001 02/10/2012 10:01:00 AM 2
E001 02/10/2012 10:25:00 AM 1
E001 02/10/2012 03:00:00 PM 2
E002 02/10/2012 03:03:00 AM 2

请帮助....
我想计算两个员工的总工作时间，以小时为单位....

任何帮助都将足够
..
谢谢.....

I have a problem with calculate total working time of an emp. where an employee can login logout in a day multiple time..
my problem is How i can calculate working hours of each employee..

here is my table data ....

EmpId Date Login/LogOutStatus

E001 02/10/2012 09:00:00 AM 1
E002 02/10/2012 09:00:00 AM 1
E001 02/10/2012 10:01:00 AM 2
E001 02/10/2012 10:25:00 AM 1
E001 02/10/2012 03:00:00 PM 2
E002 02/10/2012 03:03:00 AM 2

pls help....
i want to calculate total working time of the both employee in hours....

any help will sufficient
..
Thanks.....

推荐答案

尝试使用类似以下内容的内容.
如果有一个表EployeeWorkLog，其列为[EmpID]，[ActionDate]和[Status].

Try to use something like the following.
If there is a table EployeeWorkLog with the columns [EmpID], [ActionDate] and [Status].

SELECT e.EmpID, CAST(e.[ActionDate] AS DATE) AS WorkDay,
       SUM( DATEPART( HOUR, CAST(e.[ActionDate] - s.[ActionDate] AS TIME) ) ) AS WrkHrs,
       SUM( DATEPART( MINUTE, CAST(e.[ActionDate] - s.[ActionDate] AS TIME) ) ) AS WrkMins
  FROM 
       (SELECT EmpID, ActionDate
          FROM EployeeWorkLog
         WHERE [Status] = 1 ) AS s
  JOIN (SELECT EmpID, ActionDate
          FROM EployeeWorkLog
         WHERE [Status] = 2 ) AS e
    ON e.EmpID = s.EmpID
 WHERE e.ActionDate = (SELECT TOP 1 ActionDate
                         FROM EployeeWorkLog
                        WHERE EmpID = e.EmpID
                          AND ActionDate > s.ActionDate)
 GROUP BY e.EmpID, CAST(e.[ActionDate] AS DATE)

您可以将日期部分组合在一起以获得单个时间列

You can combine dateparts together to get single time column

尝试一下，

try this,

select EmpID,date, convert(varchar,sum(datediff(MINUTE,stm,etm))/60) + ':' + convert(varchar,sum(datediff(MINUTE,stm,etm)) % 60) as hrs from
(
    SELECT
        EmpID,
        convert(datetime,convert(varchar(10),a.date,102)) as date,
        date as etm,
        (
            SELECT top 1 date
            FROM log
            WHERE [Status] = 1 and date<= a.date and empid=a.empid and convert(datetime,convert(varchar(10),a.date,102))=convert(datetime,convert(varchar(10),date,102)) order by date desc
        ) as stm
    FROM log as a
    WHERE [Status] = 2
)as a
where stm is not null
group by empid,date

如果您不希望按日期工作，请删除带下划线的字段

祝您编码愉快！
:)

remove underlined field if you don''t want date-wise working hours

Happy Coding!
:)

这篇关于使用sql计算总工作时间(以分钟为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！