Skip to content
Advertisement

Retrieve data from two different rows in a single SQL query?

I’ve a table with the following data

enter image description here

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.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement