计算两条记录之间的“时间差" [英] Calculating 'time Difference' Between Two Records

问题描述

你好朋友，

我试图以以下格式查找报告

jobno | TimeDiff

用于下表

Hello friends,

I was trying to find report in format

jobno | TimeDiff

for the following table

actionhistoryid	jobno	actiondate	        actionstatus
910891	        206448	2011-05-13 10:31:18.877	    33
910931	        206448	2011-05-13 10:38:43.673	     8

其中actionstatus值代表33(营销)，8(批准)
TimeDiff是时间段，该作业被发送给市场营销部门以及该工作已被市场营销部门批准

谁能告诉我，我该如何写查询?

在此先感谢

Where actionstatus value stands for 33(For-Marketing),8(Approve)
TimeDiff is time period, when the job was send For Marketing and when the job is Approved by marketing

can anyone tell me, how can i write query for the same?

Thanks in advance

推荐答案

使用内部联接的另一种方法

another way using inner join

select a.jobno, datediff(s, a.actiondate, b.actiondate)
from table a
inner join table b on a.jobno = b.jobno and a.actionhistoryid != b.actionhistoryid
and a.actionstatus = 33
where a.jobno = @jobno

会以秒为单位给您带来差异

Will give you the difference in seconds

SELECT 
      jobno, 
      TimeDifference =  datediff(decimal(5,2), 0, (startTime - endTime))
FROM
(
      SELECT
            startTime = convert(datetime, select actiondate from table where ( jobno = jonId and actionstatus = 33 )),
            endTime   = convert(datetime, select actiondate from table where ( jobno = jobId and actionstatus = 8 ))
)

假设您具有特定的jobId值，这应该可以为您解决问题.

否则，您需要进行少量修改就可以对代码进行微调.

使用datediff函数，您可以获取以分钟，秒和毫秒为单位的时差.

您可以将其作为第一个参数.

datediff ( format, startDate, endDate )

BR//
Harsha

This should do the trick for you assuming you have a specific value for jobId.

Else you need to fine tune the code with small modification.

With datediff function you can get the time difference in Minutes, Seconds and Milliseconds.

This you can mention as the first parameter.

datediff ( format, startDate, endDate )

BR//
Harsha

使用datdiff ...

尝试此

use datdiff...

try this

这篇关于计算两条记录之间的“时间差"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！