Datediff and aggregate
<p>I am new to SQL so please excuse my lack of knowledge.  This is the table i have based on the following statement:</p>

<pre><code>    'select S_OPERATION.OPERATIONID, CHANGE_H.SERVICEREQNO, CHANGE_H.UPDATEDDATE
from sunrise.S_OPERATION inner join
CHANGE_H on S_OPERATION.OPERATIONID = CHANGE_H.OPERATIONID
where (S_OPERATION.OPERATIONID = 102005212) OR
                         (S_OPERATION.OPERATIONID = 102005218) or
                         (s_operation.operationid = 102005406) or
                         (s_operation.operationid = 102005401) or
                         (s_operation.operationid = 102005215)'
</code></pre>

<p><a href="http://i.stack.imgur.com/SVCkN.jpg" rel="nofollow">enter image description here</a></p>

<p>I would like to be able to calculate the time difference between events within the same job.</p>

<p>Please note: OperationID=event, Servicereqno=job</p>

<p>My end goal is to calculate the average time taken between each event and export this into a report, but i am having problems getting past the first hurdle.</p>

<p>I have tried the following statement however it does not work:</p>

<pre><code>WITH cteOps AS 
(  
  SELECT 
    row_number() OVER (PARTITION BY change.servicereqid ORDER BY change.updateddate) seqid,
    updateddate, 
    servicereqid 
  FROM CHANGE.updateddate, CHANGE.addedby, S_OPERATION.operationid, CHANGE.servicereqid
)
SELECT 
  DATEDIFF(millisecond, o1.updateddate, o2.updateddate) updateddatediff, 
  servicereqid 
FROM cteOps o1 
JOIN cteOps o2 ON o1.seqid=o2.seqid+1 AND o1.servicereqid=o2.servicereqid;
</code></pre>

<p>Many thanks in advance.</p>