在sql中计算时间超过24小时 [英] Calculation of time more than 24 hours in sql

问题描述

我的employee_task_table中有一个名为Proposal_hours的列，它包含不同的值，如'10：00：00'，'20：00：00'等，[为特定任务分配的时间]。

我想总结所有这些价值并获取提案时间大于'42：30：00'的员工名单。



亲切引导我完成这个。





提前致谢。

I have a column called Proposal_hours in my employee_task_table,which consists of different values like '10:00:00','20:00:00',etc.,[allocated time for a particular task].
I want to sum up all these values and fetch the list of employees whose proposal hours is greater than '42:30:00'.

Kindly guide me through this.


Thanks in advance.

推荐答案

请看一下例子：

Please, have a look at example:

DECLARE @employee_task_table TABLE (empid INT, taskid INT, alot VARCHAR(30))

INSERT INTO @employee_task_table (empid, taskid, alot)
VALUES(1,1,'10:00:00'), (1,1,'20:00:00'), (1,1,'05:35:00'),
(1,2,'00:55:00'), (1,2,'01:05:00'), (2,1,'10:00:00'),
(2,1,'15:15:00'), (3,3,'13:15:00'), (3,3,'20:45:00'),
(2,3,'40:00:00'), (2,3,'00:22:00'), (3,3,'03:10:00')

SELECT empid, taskid, SUM(Hrs) AS TotHrs, SUM(Mns) AS TotMns, SUM(Scs) AS TotScs
FROM (
    SELECT empid, taskid, CONVERT(INT, LEFT(alot, CHARINDEX(':', alot)-1)) AS Hrs,
            CONVERT(INT, SUBSTRING(alot, CHARINDEX(':', alot)+1, 2)) As Mns,
            CONVERT(INT, SUBSTRING(alot, CHARINDEX(':', alot, 5)+1, 2)) AS Scs
    FROM @employee_task_table
) AS T
GROUP BY empid, taskid

结果：

Result:

empid	taskid	TotHrs	TotMns	TotScs
1		1		35		35		0
2		1		25		15		0
1		2		1		60		0
2		3		40		22		0
3		3		36		70		0

其余属于你！

The rest belongs to you!

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