1

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.

Community
  • 1
  • 1
mike100111
  • 920
  • 8
  • 16
  • is it confirm only one single record of `Starting` and `Finished` per JobNum ? Looks like your data doesn't. – Squirrel Jul 22 '16 at 02:38

1 Answers1

2

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]
sgeddes
  • 62,311
  • 6
  • 61
  • 83