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

Question

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

• BCdotWEB: Please follow the guidelines @ codereview.stackexchange.com/help/how-to-ask for titling your question.

Answer 1

You can use Sql Server's window functions LEAD and LAG to get access to two rows in one SELECT statement.

Here's just one way to get the results you're after:

DECLARE @jobs TABLE
(
 Job char(1) primary key,
 Start_time datetime,
 End_time datetime
);
INSERT @jobs VALUES
('A', '2020/01/10 8:00', '2020/01/10 8:15'),
('B', '2020/01/10 8:15', '2020/01/10 8:17'),
('C', '2020/01/10 8:17', '2020/01/10 8:19'),
('D', '2020/01/10 8:19', '2020/01/10 8:53');
SELECT Job,
 Start_time,
 [Other job],
 [Other job's end time],
 DATEDIFF(mi, Start_time, [Other job's end time]) [Diff (min)]
FROM
(
 SELECT
 Job, 
 Start_time,
 LEAD(Job, 2) OVER (ORDER BY Start_time) [Other job],
 LEAD(End_time, 2) OVER (ORDER BY Start_time) [Other job's end time]
 FROM @jobs
) AS data
WHERE [Other job] IS NOT NULL

Which shows:

Job Start_time Other job Other job's end time Diff (min)
---- ----------------------- --------- ----------------------- -----------
A 2020年01月10日 08:00:00.000 C 2020年01月10日 08:19:00.000 19
B 2020年01月10日 08:15:00.000 D 2020年01月10日 08:53:00.000 38

You can output the difference between two specific jobs by filtering appropriately:

SELECT Job,
 Start_time,
 [Other job],
 [Other job's end time],
 DATEDIFF(mi, Start_time, [Other job's end time]) [Diff (min)]
FROM
(
 SELECT
 Job, 
 Start_time,
 LEAD(Job) OVER (ORDER BY Start_time) [Other job],
 LEAD(End_time) OVER (ORDER BY Start_time) [Other job's end time]
 FROM @jobs
 WHERE Job IN ('A', 'C')
) AS data
WHERE Job = 'A'

Which shows:

Job Start_time Other job Other job's end time Diff (min)
---- ----------------------- --------- ----------------------- -----------
A 2020年01月10日 08:00:00.000 C 2020年01月10日 08:19:00.000 19