Question :
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.
Answer :
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 > 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 > 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 > 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