sql server - Date difference between events in same column -

i new ms sql , having difficulty in getting correct results. have table headers updateddate(date), addedby(number), operationid(number) , servicereqno(number) have been selected 3 different tables.

the operationid event , servicereqno job number. want able time difference taken between events , once established calculate average.

for example:

• opid 512 @ 20:15 - servicereqno 1
• opid 535 @ 21:23 - servicereqno 1

my first task determine difference in time is, ensuring within same job number.

many thanks

in order timespan between events, need number operations sequentially (one increments without leftout), , join on offset of 1. you'll n-1 rows result timespan inbetween.

something this:

with cteops (   select row_number() on (partition servicereqno order updateddate) seqid, updateddate, servicereqno   yourdatasource ) select datediff(millisecond, o1.updateddate, o2.updateddate) updateddatediff, servicereqno   cteops o1   join cteops o2 on o1.seqid=o2.seqid+1 , o1.servicereqno=o2.servicereqno; 

of course can perform aggregates on average or whatever need.