CODEWITHSUNDEEP
sqlsql-server
nsivakr
nsivakr

Reputation: 1595

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.

Upvotes: 0

Views: 79

Answers (3)

lareb
lareb

Reputation: 92

This will work for you:

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');

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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.

Upvotes: 3

eavom
eavom

Reputation: 1097

This should work as per your need.

;WITH JobTable AS
(
    SELECT *
    FROM (VALUES('A', CAST('10/10/2020 08:00' AS DATETIME), CAST('10/10/2020 08:15' AS DATETIME))
                , ('B', CAST('10/10/2020 08:15' AS DATETIME), CAST('10/10/2020 08:17' AS DATETIME))
                , ('C', CAST('10/10/2020 08:17' AS DATETIME), CAST('10/10/2020 08:19' AS DATETIME))
        ) AS D (Job, Start_Time, End_Time) 
        
)
SELECT DATEDIFF (MINUTE 
                 , (MAX(CASE WHEN Job = 'A' THEN Start_Time END))
                 , (MAX(CASE WHEN Job = 'C' THEN End_Time END))
                )
FROM JobTable Data

Upvotes: 1

Related Questions