The Calculation of Chute Time

Chute time is the period from crew accepts the task to the engine first started before actual departure in emergency services field. In terms of reviewing historic data, the point of actual departure usually is quite clear. However, the point of crew accepts is ambiguous. It is norm in aviation industry there are several accepts and declines co-existing for a trip. The following algorithm may help to determine this point:

The oldest accept event which is newer than newest decline event.

However, if the working system does flag the last accepting event being the true acceptance, for which the crew being occupied, then the last event should be used.

Following is a real example in T-SQL how to calculate chute time based on IQM and based on first approach:

USE ArchiveOPTIMAS
GO

IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_Legs'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_Legs
IF EXISTS( SELECT *
FROM QM.SYS.TABLES
WHERE NAME = 'SSRS_Program_Temp_CrewTime'
)
DROP TABLE QM.dbo.SSRS_Program_Temp_CrewTime

DECLARE @Type VARCHAR(4)
DECLARE @Con VARCHAR(1)
SET @Type = 'URG'
SET @Con = 'D'
SELECT DISTINCT trip.trip_id
, t.transport_id
, tl.actual_departure AS Patient_Leg_Departure
, tl.actual_arrival AS Patient_Leg_Arrival
INTO QM.dbo.SSRS_Program_Temp_Legs
FROM dbo.transport AS t
INNER JOIN dbo.trip_leg_patient AS tlp ON tlp.transport_id = t.transport_id
INNER JOIN dbo.trip_leg AS tl ON tl.trip_leg_id = tlp.trip_leg_id
INNER JOIN dbo.trip ON trip.trip_id = tl.trip_id
INNER JOIN QM.dbo.dw_Legs AS dl on dl.Patient_id = t.transport_id
WHERE tl.trip_leg_status_cd = 'COMP'
AND t.transport_status_cd = 'COMP'
AND tl.actual_departure IS NOT NULL
AND tl.actual_arrival IS NOT NULL
AND t.transport_priority_cd = @Type
AND dl.Contract = @Con

SELECT Rest.trip_id AS Trip_Id
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure)>0 THEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.Prior_Departure) ELSE NULL END AS S1_2
, CASE WHEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.Prior_Arrival) ELSE NULL END AS S2_3
, CASE WHEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Arrival, Rest.Patient_Leg_Departure) ELSE NULL END AS S3_4
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Departure, Rest.Patient_Leg_Arrival) ELSE NULL END AS S4_5
, CASE WHEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Patient_Leg_Arrival, Rest.After_Departure) ELSE NULL END AS S5_6
, CASE WHEN DATEDIFF(MI, Chute_Time.Crew_Accepts, Rest.After_Departure)>0 THEN DATEDIFF(MI, Rest.Prior_Departure, Rest.After_Departure) ELSE NULL END AS S1_6
INTO QM.dbo.SSRS_Program_Temp_CrewTime
FROM (
SELECT sptl.trip_id
, MIN(th.add_datetime) AS Crew_Accepts
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
INNER JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
INNER JOIN
( SELECT sptl.trip_id
, MAX( CASE WHEN th.add_datetime IS NOT NULL AND te.transport_event_id = 129
THEN th.add_datetime
ELSE DATEADD(DAY, 1, '1900-01-01')
END
) AS Crew_Decline
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
LEFT JOIN dbo.transport_history AS th ON th.transport_id = sptl.transport_id
LEFT JOIN dbo.transport_event AS te ON te.transport_event_id = th.transport_event_id
GROUP BY sptl.trip_id
) AS Decline_Events
ON Decline_Events.trip_id = sptl.trip_id
WHERE th.add_datetime >= Decline_Events.Crew_Decline
AND te.transport_event_id = 130
GROUP BY sptl.trip_id
) AS Chute_Time
FULL JOIN
( SELECT sptl.trip_id
, Before_After.Prior_Departure
, Before_After.Prior_Arrival
, sptl.Patient_Leg_Departure
, sptl.Patient_Leg_Arrival
, Before_After.After_Departure
FROM ( SELECT sptl.trip_id
, MAX(CASE WHEN tl.actual_departure < sptl.Patient_Leg_Departure THEN tl.actual_departure END) AS Prior_Departure
, MAX(CASE WHEN tl.actual_arrival < sptl.Patient_Leg_Departure THEN tl.actual_arrival END) AS Prior_Arrival
, MIN(CASE WHEN tl.actual_departure > sptl.Patient_Leg_Arrival THEN tl.actual_departure END) AS After_Departure
FROM QM.dbo.SSRS_Program_Temp_Legs AS sptl
INNER JOIN dbo.trip_leg AS tl ON tl.trip_id = sptl.trip_id
GROUP BY sptl.trip_id
) AS Before_After
INNER JOIN QM.dbo.SSRS_Program_Temp_Legs AS sptl
ON sptl.trip_id = Before_After.trip_id
) AS Rest
ON Rest.trip_id = Chute_Time.trip_id

SELECT SUM(IQM_Sep) AS IQM
, SUM(Stdevp_Sep) AS Stdevp
FROM ( SELECT AVG(S1_2) AS IQM_Sep
, NULL AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime AS Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN (SELECT TOP 25 PERCENT Trip_Id FROM QM.dbo.SSRS_Program_Temp_CrewTime WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)
UNION
SELECT NULL AS IQM_Sep
, STDEVP(S1_2) AS Stdevp_Sep
FROM QM.dbo.SSRS_Program_Temp_CrewTime
) AS Colum


http://www.math.carleton.ca/CanQueue-08/canq08Alanis.pdf