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

Conservative Forecast Based On Historic Data

You can always use mean on the historic data as your forecast, which is reasonable. However, this is radical, especially when not consider the probability distribution. To be conservative, you may need to add a standard deviation (σ):

Mean + σ
Or
Mean - σ

In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.

If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).

In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).

http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp

Practical Mean

In statistics, mean has two related meanings:
- the arithmetic mean.
- the expected value of a random variable, which is also called the population mean.

To get the mean for a column in database, in general built-in aggregation function AVG() is good enough. However, one much understand this function is of arithmetic mean. If your database contains noise data, this is not good enough, simply because this mean does not have noise data prune mechanism. While different means would have their precise meaning, if you do not know which to use, use interquartile mean (IQM).

Following is the some notes related to T-SQL’s mean functions:

Arithmetic mean (AM)
There is built-in function – Never use DISTINCT, and no need to use ALL because it is default:

AVG ( [ ALL | DISTINCT ] expression )

Median
Median is not mean, but it is included here anyway. There is no such function in T-SQL. But you can get from here (credits to Itzik Ben-Gan):

SELECT(
(SELECT MAX(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1)
+
(SELECT MIN(Value) FROM
(SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value DESC) AS H2)
) / 2 AS Median;

Interquartile mean (IQM)
There is no such function in T-SQL. But you can get from here:

SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2)
AND Minut.Trip_Id NOT IN
(SELECT TOP 25 PERCENT Trip_Id FROM Minut WHERE S1_2 IS NOT NULL ORDER BY S1_2 DESC)

If the dataset is so small, such as only a few records exist, SELECT TOP 25 PERCENT won't work. Instead, use following solution:

SELECT AVG(S1_2) AS P1_2
FROM Minut
WHERE S1_2 IS NOT NULL
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2
)
AND Minut.Trip_Id NOT IN
(
SELECT TOP
(
SELECT CONVERT(INT, COUNT(*)/4)
FROM Minut
WHERE S1_2 IS NOT NULL
) Trip_Id
FROM Minut
WHERE S1_2 IS NOT NULL
ORDER BY S1_2 DESC
)


http://en.wikipedia.org/wiki/Mean
http://blogical.se/blogs/mikael_sand/archive/2009/07/24/t-sql-is-mean-there-is-no-median.aspx
http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005.aspx
http://koncordpartners.blogspot.com/2010/12/rounding-issue-in-select-top-n-percent.html