如何以分钟为单位计算开始时间和结束时间之间的差异总和 [英] how to caluculate sum of diffrence between start time and end time in minuts:seconds

问题描述

USE [irdamay3]
GO
/****** Object:  StoredProcedure [dbo].[GetTestSummaryChapterwiseByUserID]    Script Date: 05/04/2012 13:12:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetTestSummaryChapterwiseByUserID] --1
@UserID bigint
As
begin
create table #temp1(createddttm datetime,name varchar(100),total int)

	insert into #temp1(createddttm,name)
	select uem.CreatedDtTm,c.Name from UserExamMap uem 
	--left join ExamDuration ED on uem.Id=ED.ExamId
	inner join ExamResponse er on er.UserExamMapId=uem.Id --where uem.UserID=1 
	inner join question q on q.id=er.QuestionID
	inner join SubTopic st on st.Id=q.SubTopicID
	inner join Topic t on t.Id=st.TopicID
	inner join Chapter c on c.Id=t.ChapterID where uem.UserID=@UserID group by uem.CreatedDtTm,c.Name 
	
create table #ExamDur(DtTm datetime,startTm datetime,EndTm datetime,total int)
insert into #ExamDur(DtTm,startTm,EndTm,total)
select UE.CreatedDtTm,ED.StartTime,ED.EndTime,
CAST(datediff(SECOND,ed.starttime,ed.EndTime)+60*DATEDIFF(minute,ed.StartTime,ed.EndTime)+3600*DATEDIFF(HOUR,ed.StartTime,ed.EndTime) as int) total
from UserExamMap UE 
left join  ExamDuration ED on UE.Id=ED.ExamId where UE.UserId=1--@UserID

update #temp1 set total=ED.total from #temp1 T inner join #ExamDur ED on T.createddttm=ED.DtTm

	
	select * from #temp1 
	
	select ROW_NUMBER()over(order by name) SNo,count(name) NoofTimesPracticed,name Chapter,
	CAST
(
(SUM (datepart(HOUR, convert (int, total, 108))) +
(sum(datepart(MINUTE, convert (int, total, 108)))/60) ) AS VARCHAR(2)
)
+ '':'' +
CAST
(
sum(datepart(MI, convert (int, total, 108))) - 60 * (sum(datepart(MINUTE, convert (int, total, 108)))/60)
 as VARCHAR(2))+
 '':''+CAST(sum(datepart(second,convert(int,total,108)))-3600*(sum(datepart(second,convert(int,total,108)))/60)as varchar(2)) ExamDuration
		from #temp1 group by name
	
select COUNT(*) Practice  from #temp1 

	
end

推荐答案

pls将上述功能的代码发送给我
否.请尝试建议的方向，自己看看.

开始时间和结束时间之间的差异，并以分钟和秒为单位显示时间
以 hh:mi:ss
的格式获取输出 在SQL Server 2008中:

pls send me the code in the above functionality
No. Please try on suggested direction and see by yourself.

difference between the starttime and endtime and display the time in minutes and seconds
To get output in the format of hh:mi:ss
In SQL Server 2008:

SELECT CAST((EndDate-StartDate) as TIME(0)) [Duration] FROM MyData

在SQL Server 2005中:

In SQL Server 2005:

SELECT RIGHT(CONVERT(varchar, CAST(EndDate – StartDate as DATETIME), 121), 12) [Duration] FROM MyData

试试吧！

Try!

这篇关于如何以分钟为单位计算开始时间和结束时间之间的差异总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！