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:
EventDate = 'Finished'
Select a.[AutoNumber], DATEDIFF(SECOND, (SELECT m.EventDate
FROM [myTable] m
WHERE m.Event = 'Starting' and a.JobNum= m.JobNum),
FROM myTable m
WHERE m.Event = 'Finished' and a.JobNum= m.JobNum)
From myTable a
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
If I'm understanding your question correctly, here's one option using
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]