SQLTeam.com | Weblogs | Forums

Query about calculating time difference between events on separate rows

sql2008

#1

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;'


#2

I think you forgot to post the statement


#3

you need to also make your 1st query as a CTE

WITH cte
AS (
	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)
	)
	,cteOps
AS (
	SELECT row_number() OVER (
			PARTITION BY servicereqid ORDER BY updateddate
			) seqid
		,updateddate
		,servicereqid
	FROM cte
	)
SELECT DATEDIFF(millisecond, o1.updateddate, o2.updateddate) updateddatediff
	,servicereqid
FROM cteOps o1
INNER JOIN cteOps o2 ON o1.seqid = o2.seqid + 1
	AND o1.servicereqid = o2.servicereqid;