Find the timestamp of a unique ticket number

I have a table that looks like this:

**ActivityNumber -- TimeStamp        -- PreviousActivityNumber -- Team**
1234-4         -- 01/01/2017 14:12 -- 1234-3                   -- Team A 

There are 400,000 rows.
The ActivityNumber is a unique ticket number with the activity count attached. There are 4 teams. Each activitynumber is in the table.

I need to calculate the average time taken between updates for each team, for each month (to see how each team is improving over time).

I produced a query which counts the number of activities per team per month – so I’m part way there.

I’m unable to find the timestamp for the previousActivityNumber so I can subtract it from the current Activity number. If I could get this, I could run an average on it.

Answer

Conceptually:

select a1.Team,
       a1.ActivityNumber, 
       a1.TimeStamp, 
       a2.Timestamp as PrevTime, 
       datediff('n',a1.Timestamp, a2.timestamp) as WorkMinutes
from MyTable a1
left join MyTable a2
on ((a1.Team = a2.Team)
and (a1.PreviousActivityNumber = a2.ActivityNumber )