Calculate the time difference in Sql Server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

SQL

---------------------------------------------------------------------------------
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

Posted 30-Apr-15 1:47am

mimtiyaz

Add a Solution

Comments

virusstorm 30-Apr-15 8:03am

Are you able to change the table structure? Your current design requires very complex SQL.

John C Rayan 30-Apr-15 8:03am

what parameter will you pass to the SP? job_id?

mimtiyaz 30-Apr-15 9:46am

job_id

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-04-30T02:31:00

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.

psima · Answer 2 · 2015-04-30T02:28:00

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

SQL

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

Calculate the time difference in Sql Server

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0