计算Sql Server中的时差 [英] Calculate the time difference in Sql Server

问题描述

你好



我有一个sql数据库表tbl_timer，其中我有五列（job_id，start_time，hold_time，resume_time，finish_time）

作业启动后，开始时间将插入表格中。同样明智的是，hold_time，resume_time，finish_time也将根据工作订单状态添加。



工作可以保持多次，并可以相应地恢复。我需要计算完成工作订单的总时差，即工作的start_time和finished_time之间的时间。



示例表

   -    ------- -------------------------------------------------- ----------------------  
 job_id start_time hold_time resume_time finish_time 
   -  -    ----------------------------------- --------------------------------------------  
 jo_1 | 2015-04-30 08:00 | NULL | NULL | NULL 
 jo_1 | NULL | 2015-04-30 08:30 | NULL | NULL 
 jo_1 | NULL | NULL | 2015- 04-30 9:15 | NULL 
 jo_1 | NULL | 2015-04-30 10:45 | NULL | NULL 
 jo_1 | NULL | NULL | 2015-05-01 8:00 | NULL 
 jo_1 | NULL | NULL | NULL | 2015-05-01 11:00 

我需要计算使用sql存储过程完成作业所需的总时间。





建议将不胜感激。



谢谢

解决方案

这是一种复杂的做事方式：如果你有两张桌子会更容易：

 Job_id 
 StartTime 
 EndTime 

和

< pre lang =text> JobId
HoldTime
ResumeTime

并且您为每个保留的开始和结束填写单个记录，并为开始和填写单个作业记录结束。

然后你可以很容易地计算出每个工作的累计保持时间，并从工作的结束时间和开始时间之间的差值中减去。

那是非常简单！



使用您的数据结构这是非常复杂的 - 因为你需要将每个保持与其匹配的简历相关联，这非常困难，除非你引入一个序列号或类似的。

请检查以下查询它显示了作业所占的总分钟数： -

 选择 job_id，start_time，hold_time，resume_time，finish_time 
，DATEDIFF（分钟，开始时间，结束时间）
 来自 
（
  select  job_id，Max（start_time）start_time，Max（hold_time）hold_time，
 Max（resume_time）resume_time，Max（finish_time）finish_time 
 来自 [时间] 
  group   by  job_id 
）  a 
 

Hello

I've a sql database table tbl_timer in which I've five columns (job_id, start_time, hold_time, resume_time, finish_time)
Once the job is started, the start time will be inserted in table. Like wise, the hold_time, resume_time, finish_time will also be added based on the job order status.

Job can be kept on hold for multiple times and can resumed accordingly. I need to calculate the total time difference for completing a job order i.e., the time between start_time and finished_time of the job.

Example Table

---------------------------------------------------------------------------------
job_id start_time          hold_time            resume_time           finish_time
---------------------------------------------------------------------------------
jo_1  |2015-04-30 08:00   |NULL                |NULL                 |NULL
jo_1  |NULL               |2015-04-30 08:30    |NULL                 |NULL
jo_1  |NULL               |NULL                |2015-04-30 9:15      |NULL   
jo_1  |NULL               |2015-04-30 10:45    |NULL                 |NULL   
jo_1  |NULL               |NULL                |2015-05-01 8:00      |NULL   
jo_1  |NULL               |NULL                |NULL                 |2015-05-01 11:00

I need to calculate the total time needed to complete a job using sql stored procedure.


Suggestions will be appreciated.

Thank You

解决方案

That is a complicated way to do things: it would be a lot easier if instead you had two tables:

Job_id
StartTime
EndTime

And

JobId 
HoldTime
ResumeTime

And you fill out a single record for a start and end of each hold, and a single job record for the start and end.
You could then work out the cumulative Hold times very easily for each job, and subtract that from the difference between the End and Start times for the job.
That's pretty trivial!

Doing it with your datastructure is a lot more complex - because you need to correlate each hold with it's matching resume, and that gets pretty difficult, unless you introduce a sequence number or similar.

Please check the below query which shows total minutes taken by the job :-

Select  job_id, start_time, hold_time, resume_time, finish_time
,DATEDIFF(minute,start_time,finish_time)
from
(
select job_id,Max(start_time) start_time,Max(hold_time) hold_time,
Max(resume_time) resume_time,Max(finish_time) finish_time
from [time]
group  by job_id
) as a

这篇关于计算Sql Server中的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！