I’ve a table with the following data
and I need to find the number of minutes between two jobs (Say A and C).
The following query works but wondering, if there is a simpler way to achieve the same.
DECLARE @StartTime datetime Declare @EndTime datetime set @StartTime = (SELECT start_time from table where jobname = 'A' ) set @EndTime = (SELECT end_time from table where jobname = 'C' ) select datediff(minute,@StartTime, @EndTime) numberOfMinutes
Goal is to achieve, the difference between start time and end time of two jobs.
Advertisement
Answer
I can interpret your question in two ways. The first would be the time difference from job A to job C. That would be the end time of A to the start time of C:
select datediff(minute, max(case when job = 'A' then endtime end), max(case when job = 'C' then starttime end) ) from t where job in ('A', 'C');
Alternatively, you might be asking: “What is the difference in duration between the jobs?”
select sum(case when job = 'A' then datediff(minute, starttime, endtime) else -datediff(minute, starttime, endtime) end) from t where job in ('A', 'C');
Both of these assume that A and C only appear once in the table. If not, you should ask a new question and be clearer about what you really want to do.