Find time between status changes for each unit on each incident I have a table called History that holds unit status changes when they are assigned to incidents. Each unit can change status from DP to AK to ER to AR. Statuses can be skipped.

The table is called history. The fields are Unit, Datetimestamp, Status, Incident.

I want to find the average time between each of the statuses for every unit for an incident. e.g. for the UNIT E04 on INCIDENT F141000001 I want to know the time between DP to AK, AK to ER, ER to AR. Then use this to derive the average time between each of these status changes for all the units in the table.
OK, try this.  I made some assumptions.  


All statuses start as DP
Status changes are only one way as specified above and don't revert the other direction.
If a status change is listed twice, use the first earliest instance
If a status change is skipped, use the previous status time stamp to calculate the change
INCIDENT is not desired as part of the AVG status change report for UNIT
Averages in the final query are based off the actual number of status changes that occurred and is variable by UNIT/INCIDENT


With those assumptions in mind I present the following solution.  LAG() isn't an option in 2008, so you are left with making a bunch of datasets to realize the output you want.  I'm using CTEs to generate the lists of UNITS by INCIDENT and then creating temp tables to work with the timestamps.  

I could have done a bunch of nested CTEs but then they'd be executed multiple times; same with doing this with sub queries. Finally, when reading the output for the status changes, if a change is preceeded by a NULL value, it is because the status was skipped.  In the case of E14, based off available sample data, there were no status changes from DP.

Hopefully this is what you need or at least enough to get you going.

GENERATE SAMPLE DATA

create table history 
(
      UNIT CHAR(3)
    , DATETIMESTAMP CHAR(16)
    , STATUS CHAR(2)
    , INCIDENT CHAR(10)
)

INSERT history
values ('E04','20140617101703ED','DP','F141000001')
     , ('L24','20140617101703ED','DP','F141000001')
     , ('E04','20140617101845ED','ER','F141000001')
     , ('L24','20140617101848ED','ER','F141000001')
     , ('E07','20140617101955ED','DP','F141000002')
     , ('L17','20140617101955ED','DP','F141000002')
     , ('E04','20140617102029ED','AR','F141000001')
     , ('L24','20140617102038ED','AR','F141000001')
     , ('E07','20140617102235ED','ER','F141000002')
     , ('L17','20140617102238ED','ER','F141000002')
     , ('E14','20140617102501ED','DP','F141000003')
     , ('D03','20140617102626ED','DP','F141000002')
     , ('E07','20140617102712ED','ER','F141000002')
     , ('L17','20140617102717ED','ER','F141000002')
     , ('D03','20140617102740ED','ER','F141000002')
     , ('D03','20140617102744ED','aR','F141000002')

--DROP TABLE history


CODE

--GATHER DP AND AK STATUSES AND COMPARE
with 
cte_dp as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'DP'
    group by UNIT, STATUS, INCIDENT
),
cte_ak as 
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AK'
    group by UNIT, STATUS, INCIDENT
)
select dp.UNIT, coalesce(ak.DATETIMESTAMP, dp.DATETIMESTAMP) PreviousTimeStamp, DATEDIFF(second,dp.DATETIMESTAMP,ak.DATETIMESTAMP) DiffInSecs, dp.INCIDENT, case when ak.UNIT is null then 0 else 1 end as StatusCount
into #dk
from cte_dp dp
    left join cte_ak ak on ak.UNIT = dp.UNIT
        and ak.INCIDENT = dp.INCIDENT
        and ak.DATETIMESTAMP &gt; dp.DATETIMESTAMP;

--GATHER ER STATUS AND COMPARE TO PREVIOUS STATUS CHANGE
with
cte_er as 
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'ER'
    group by UNIT, STATUS, INCIDENT
)
select dk.UNIT, coalesce(er.DATETIMESTAMP, dk.PreviousTimeStamp) PreviousTimeStamp, DATEDIFF(second,dk.PreviousTimeStamp,er.DATETIMESTAMP) DiffInSecs, dk.INCIDENT, case when er.UNIT is null then 0 else 1 end as StatusCount
into #kr
from #dk dk
    left join cte_er er on er.UNIT = dk.UNIT
            and er.INCIDENT = dk.INCIDENT
            and er.DATETIMESTAMP &gt; dk.PreviousTimeStamp;

--GATHER AR STATUS AND COMPARE WITH PREVIOUS STATUS CHANGE
with
cte_ar as
(
    select UNIT, min(convert(datetime,left(DATETIMESTAMP,4)+'-'+SUBSTRING(DATETIMESTAMP,5,2)+'-'+SUBSTRING(DATETIMESTAMP,7,2)+' '+SUBSTRING(DATETIMESTAMP,9,2)+':'+SUBSTRING(DATETIMESTAMP,11,2)+':'+SUBSTRING(DATETIMESTAMP,13,2))) DATETIMESTAMP, STATUS, INCIDENT
    from history
    where STATUS = 'AR'
    group by UNIT, STATUS, INCIDENT
)
select kr.UNIT, DATEDIFF(second,kr.PreviousTimeStamp,ar.DATETIMESTAMP) DiffInSecs, kr.INCIDENT, case when ar.UNIT is null then 0 else 1 end as StatusCount
into #rr
from #kr kr
    left join cte_ar ar on ar.UNIT = kr.UNIT
            and ar.INCIDENT = kr.INCIDENT
            and ar.DATETIMESTAMP &gt; kr.PreviousTimeStamp;

--REPORT THE STATUS CHANGE TIMES IN SECONDS FOR EACH STATUS CHANGE, PER UNIT, PER INCIDENT
select dk.UNIT, dk.DiffInSecs 'DP-AK', kr.DiffInSecs 'AK-ER', rr.DiffInSecs 'ER-AR', dk.INCIDENT
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT and kr.INCIDENT = dk.INCIDENT
    left join #rr rr on rr.UNIT = dk.UNIT and rr.INCIDENT = dk.INCIDENT

--REPORT THE AVG TIME THE STATUS CHANGED FOR EACH UNIT
select dk.UNIT, (isnull(dk.DiffInSecs,0)+isnull(kr.DiffInSecs,0)+isnull(rr.DiffInSecs,0))/(case when (dk.StatusCount+kr.StatusCount+rr.StatusCount) = 0 then 1 else (dk.StatusCount+kr.StatusCount+rr.StatusCount) end) AverageSeconds_Between_StatusChange
from #dk dk
    left join #kr kr on kr.UNIT = dk.UNIT
    left join #rr rr on rr.UNIT = dk.UNIT

drop table #dk
drop table #kr
drop table #rr