1
\$\begingroup\$

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
\$\endgroup\$
1
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.