mike100111 mike100111 - 1 year ago 73
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:


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
field where
is the same. Because there can be multiple events for a single
, I also need to specify that
is the same and to only subtract the row where
EventDate= 'Starting'
from the row with
EventDate = 'Finished'
(There will always only be one
and one
event per

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 Source

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

Select [AutoNumber], 
                max(case when event = 'starting' then EventDate end),
                max(case when event = 'Finished' then EventDate end)
From myTable a 
Group By [AutoNumber]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download