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