I am new to SQL so please excuse my ignorance. I would like to be able to calculate the time difference between events within the same job.
UPDATEDDATE OPERATIONID SERVICEREQID
2015-02-06 14:42:58.523 102005212 CH000003
2014-12-04 11:53:32.277 102005218 CH000003
2015-04-23 15:51:09.317 102005401 CH000003
2014-06-23 09:26:22.420 102005406 CH000003
2014-06-17 07:37:33.647 102005212 CH000008
2014-07-25 14:50:49.640 102005218 CH000008
Please note: OperationID=event, Servicereqid=job
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.
I have tried the following statement however it does not work: i am getting the error message Invalid object name CHANGE.updateddate
'SELECT CHANGE.UPDATEDDATE, S_OPERATION.OPERATIONID, CHANGE.SERVICEREQID
FROM CHANGE CROSS JOIN
S_OPERATION
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);
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;'