mike100111 mike100111 - 4 months ago 10
SQL Question

Subtract two dates with matching field, and another specific field value in SQL Table

I need to get the time difference between two events represented as rows in the same table. Among others, the row has the following fields:

JobNum
EventDate
Event


The
Event
field is used to see if the event is the start or finish for that Job.

I need to get the time differences between the
EventDate
field where
JobNum
is the same. Because there can be multiple events for a single
JobNum
, I also need to specify that
JobNum
is the same and to only subtract the row where
EventDate= 'Starting'
from the row with
EventDate = 'Finished'
(There will always only be one
Starting
and one
Finished
event per
JobNum
).

The following is as far as i have gotten but its not working:

Select a.[AutoNumber], DATEDIFF(SECOND, (SELECT m.EventDate
FROM [myTable] m
WHERE m.Event = 'Starting' and a.JobNum= m.JobNum),
(SELECT m.EventDate
FROM myTable m
WHERE m.Event = 'Finished' and a.JobNum= m.JobNum)
)
From myTable a


It partially runs, but then gives the following error:


Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


I also took a look at this question but couldn't figure out how to modify the code to work for my situation. How to get difference between two rows for a column field?

Any help would be greatly appreciated.

Answer

If I'm understanding your question correctly, here's one option using conditional aggregation:

Select [AutoNumber], 
       datediff(second, 
                max(case when event = 'starting' then EventDate end),
                max(case when event = 'Finished' then EventDate end)
       )
From myTable a 
Group By [AutoNumber]
Comments