How to calculate 90th Percentile on time (either MM:SS or seconds )

Question

SELECT DISTINCT

 ,DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.IncidentDate) AS CalltakerProcessingTime ,DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.FirstUnitDispatchedTime) AS TotalProcessingTime
 ,DATEDIFF(s,MV_Incident.IncidentDate,MV_Incident.FirstUnitDispatchedTime) AS GatekeeperProcessingTime

FROM
  MV_Incident

I am attempting to calculate the 90th percentile for time frames obtained from DATEDIFF. I am open to doing this in either the syntax of the query or in an expression in Report Builder. I know it is possible to do in excel but I am hopeful there is a way to achieve this automatically with a query or expression.

I am looking at a list of results like this:

03:11
02:09
01:35
00:57
02:36

90th percentile 02:57 (according to excel).

Answer 1

Here is an example of how you can do this. I remove the DISTINCT, because DISTINCT could have some interesting effects when computing percentiles.

; WITH CTE AS (
   SELECT DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.IncidentDate)            AS CalltakerProcessingTime,
          DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.FirstUnitDispatchedTime) AS TotalProcessingTime,
          DATEDIFF(s,MV_Incident.IncidentDate,MV_Incident.FirstUnitDispatchedTime)            AS GatekeeperProcessingTime
   FROM  MV_Incident
), percenttiles AS (
   SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY CalltakerProcessingTime)  OVER() AS CalltakerProcessingTime,
          percentile_cont(0.9) WITHIN GROUP (ORDER BY TotalProcessingTime)      OVER() AS TotalProcessingTime,
          percentile_cont(0.9) WITHIN GROUP (ORDER BY GatekeeperProcessingTime) OVER() AS GatekeeperProcessingTime
   FROM CTE
)
SELECT TOP (1) GatekeeperProcessingTime, TotalProcessingTime, GatekeeperProcessingTime
FROM   percentiles

Answer 2

Hi anonymous user,

You could use PERCENTILE_CONT in your situation.

Please refer below example:

CREATE TABLE #percentile   
(  
[TIME] DATETIME  
)  
  
INSERT INTO #percentile VALUES  
('2020-09-01 03:11:00'),  
('2020-09-01 02:09:00'),  
('2020-09-01 01:35:00'),  
('2020-09-01 00:57:00'),  
('2020-09-01 02:36:00')  
  
;WITH CTE AS (  
SELECT top 1 P90= PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY DATEDIFF(s,(select MIN(TIME) from #percentile),time)) OVER () FROM #percentile)  
select DATEADD(SECOND,P90,(select MIN(TIME) from #percentile )) P90 FROM CTE  

Output:
P90
2020-09-01 02:57:00.000

In addition, you could refer below query and check whether it helps.

SELECT DISTINCT   
 percentile_cont(0.9) WITHIN GROUP (ORDER BY DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.IncidentDate))  OVER() AS CalltakerProcessingTime,  
percentile_cont(0.9) WITHIN GROUP (ORDER BY DATEDIFF(s,MV_Incident.IncidentStartedDateTime,MV_Incident.FirstUnitDispatchedTime))      OVER() AS TotalProcessingTime,  
percentile_cont(0.9) WITHIN GROUP (ORDER BY DATEDIFF(s,MV_Incident.IncidentDate,MV_Incident.FirstUnitDispatchedTime)) OVER() AS GatekeeperProcessingTime  
FROM  MV_Incident  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.