如何找到同一列中两个日期之间的差异？

I have a table SO_STATUS that writes a record for each status change for a service order (we'll call the Service_Order_ID "Job_ID"). Job_ID references SERVICE_ORDER table. When the service order is initialized, a record is written for that status type of "open" (StatusType 2) which shows the datetime. Then another record is written in the status table for when it is "in progress" (StatusType 1). And also when the service order is "closed", another record written in the status table (StatusType 3). There are also other status types that may happen, but these are the most common. The data in the SO_STATUS table looks like this:

我有一个表SO_STATUS,它为服务订单的每个状态更改写一条记录(我们将调用Service_Order_ID“Job_ID”)。 Job_ID引用SERVICE_ORDER表。初始化服务订单时,将为状态类型“打开”(状态类型2)写入记录,该记录显示日期时间。然后在状态表中写入另一条记录,用于何时“正在进行”(StatusType 1)。而且当服务订单“关闭”时,另一条记录写在状态表(StatusType 3)中。还有其他可能发生的状态类型,但这些是最常见的。 SO_STATUS表中的数据如下所示:

id            Date                Job_ID  StatusTypeID  EmployeeID
1     2012-01-01 09:05:00.000       51        2             5
2     2012-01-01 10:00:00.000       52        2            12
3     2012-01-01 10:01:00.000       51        1             5
4     2012-01-01 12:15:00.000       53        2             8
5     2012-01-01 12:16:00.000       51        3             5
6     2012-01-01 13:00:00.000       52        1            12
7     2012-01-01 14:00:00.000       52        3            12
8     2012-01-01 14:15:00.000       53        1             8
9     2012-01-01 15:00:00.000       54        2            11
10    2012-01-01 16:30:00.000       53        3             8
11    2012-01-01 15:00:00.000       54        1            11
12    2012-01-01 16:30:00.000       54        3            11

I need to be able to find the time elapsed between each status change of each Job_ID. Essentially, the duration of time spent from open to close for the job.

我需要能够找到每个Job_ID的每次状态更改之间经过的时间。基本上,从工作的开放到结束所花费的时间。

Output would look something like (EmployeeName would be referenced from the EMPLOYEE table):

输出看起来像(EmployeeName将从EMPLOYEE表中引用):

Job_ID       Duration    EmployeeName
  51         03:11:00        Kyle
  52         04:00:00        Chris
  53         04:15:00        Fred
  54         01:30:00        John

How would I go about getting this type of output? Thank you.

我如何获得这种类型的输出?谢谢。

7 个解决方案

#1

Why dont you use:

为什么不使用:

SELECT DATEDIFF (anyparticularunit, ' 2012-01-01 09:05:00.000', ' 2012-01-01 15:00:00.000')

Go through following link for datediff:

通过以下链接获取日期:

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Also follow this link to get different exmples:

也可以点击此链接获得不同的例子:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56126

Hope you will put further where conditions.

希望你能把条件放得更远。

#2

This this one -

这一个 -

SET NOCOUNT ON;

DECLARE @duration TABLE 
(
      id BIGINT IDENTITY
    , [date] DATETIME
    , job_id INT
    , [status] VARCHAR(10)
    , employee_id INT
)

INSERT INTO @duration ([date], job_id, [status], employee_id)
VALUES
    ('2012-01-01 09:05:00.000', 51, 'open', 5),
    ('2012-01-01 10:00:00.000', 52, 'open', 12),
    ('2012-01-01 10:01:00.000', 51, 'inprogress', 5),
    ('2012-01-01 12:15:00.000', 53, 'open', 8),
    ('2012-01-01 12:16:00.000', 51, 'closed', 5),
    ('2012-01-01 13:00:00.000', 52, 'inprogress', 12),
    ('2012-01-01 14:00:00.000', 52, 'closed', 12),
    ('2012-01-01 14:15:00.000', 53, 'inprogress', 8),
    ('2012-01-01 15:00:00.000', 54, 'open', 11),
    ('2012-01-01 16:30:00.000', 53, 'closed', 8),
    ('2012-01-01 15:00:00.000', 54, 'inprogress', 11),
    ('2012-01-01 16:30:00.000', 54, 'closed', 11)

SELECT 
      job_id
    , employee_id
    , work_time = CONVERT(VARCHAR(12), MAX([date]) - MIN([date]), 114) 
FROM @duration
GROUP BY job_id, employee_id

#3

You can use DATEDIFF to return the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate (see http://msdn.microsoft.com/en-us/library/ms189794.aspx)

您可以使用DATEDIFF返回指定的startdate和enddate之间交叉的指定日期部分边界的计数(有符号整数)(请参阅http://msdn.microsoft.com/en-us/library/ms189794.aspx)

 SELECT Job_ID,
 DATEDIFF(day, (SELECT MIN(Date) FROM YOUTABLE WHERE Job_ID=k.Job_ID),(SELECT MAX(Date) FROM YOUTABLE WHERE Job_ID=k.Job_ID)),
 (SELECT EmployeeName FROM EmployeeTABLE WHERE EmployeeID=k.EmployeeID)) FROM YOUTABLE k

#4

If your database is Oracle, you can do like this

如果您的数据库是Oracle,您可以这样做

SELECT DISTINCT JOB_ID, MAX(DATE) OVER(PARTITION BY JOB_ID)-MIN(DATE) OVER(PARTITION BY JOB_ID) AS Duration FROM TA JOIN TB .....

#5

I have created some custom code to create dat and time difference, using datediff function and dividing with certain numbers to generate hours, minutes and seconds:

我创建了一些自定义代码来创建数据和时间差,使用datediff函数并用特定数字除以生成小时,分钟和秒:

SELECT 
    Job_ID,
    CAST(DATEDIFF(second, MIN(Date), MAX(Date)) / 3600 AS VARCHAR)
    + ':' + CAST((DATEDIFF(second, MIN(Date), MAX(Date)) % 3600) / 60 AS VARCHAR)
    + ':' + CAST(((DATEDIFF(second, MIN(Date), MAX(Date)) % 3600) % 60)  AS VARCHAR)
FROM YOUTABLE 
GROUP BY Job_ID

#6

Try query given below:

尝试查询以下内容:

Select t1.Job_ID,
Convert(varchar(5),DateDiff(HH,Min(t1.JobDate),tbl.MaxDate))+' : '+convert(varchar(5),DateDiff(s,Min(t1.JobDate),tbl.MaxDate) % 3600/60)+' : '+Convert(varchar(5),DateDiff(s,Min(t1.JobDate),
tbl.MaxDate) % 60) MinDate,t1.EmployeeName From SO_STATUS t1
Inner join (Select Max(JobDate) MaxDate, job_id From SO_STATUS  Group By Job_Id)tbl on t1.Job_ID=tbl.Job_ID
Inner Join EMPLOYEE e On e.EmployeeID=t1.EmployeeID
Group By t1.EmployeeName,tbl.MaxDate,t1.Job_ID
Order By t1.Job_ID

#7

Difference between two dates of different tables which has datetime format.

SELECT t1.Column_Names,

CONVERT(varchar(10),t1.CreatedOn,103)

AS CreatedOn FROM table1 t1 INNER JOIN table2 t2

AS CreatedOn FROM table1 t1 INNER JOIN table2 t2

ON t1.id = t2.id WHERE CAST (t1.CreatedOn as Date)

ON t1.id = t2.id WHERE CAST(t1.CreatedOn as Date)

BETWEEN @fromdate and @todate.

在@fromdate和@todate之间。

i have taken t1.CreatedOn as my table attribute which holds date.

我把t1.CreatedOn作为我的table属性保存日期。

@fromdate and @todate to pass dates.

@fromdate和@todate传递日期。