none
Replace average with median

    Question

  • Does anyone know how I can change the following script so that it is bringing me back the median times as opposed to the average.

    SELECT
    DATEPART(YEAR,TIME_TO) AS [Year],
    DATEPART(MONTH,TIME_TO) AS [Month],
    DEPT_CODE AS Speciality,
    W_CODE,
    BED_CODE,
    DATEDIFF(mi, TIME_TO , NEXT_EVENT_TIME_IN)  AS [Bed Turnaround Time],
    ROW_NUMBER() OVER (PARTITION BY DATEPART(YEAR,TIME_TO), DATEPART(MONTH,TIME_TO), DEPT_CODE ORDER BY DATEPART(YEAR,TIME_TO), DATEPART(MONTH,TIME_TO) DESC) AS CNT
    INTO #TEMP
    FROM [HSIU_PROD].[rph].[vwTOPASWardMovements]
    WHERE
    TIME_TO >= '2011-01-01' AND TIME_TO < '2013-01-04'
    ORDER BY
    [Year], [Month], Speciality
    SELECT 
    [Year], 
    [Month], 
    Speciality, 
    SUM([Bed Turnaround Time]) AS [Minutes], 
    MAX(CNT) AS [Beds Used], 
    SUM([Bed Turnaround Time]) / MAX(CNT) AS [AVG Turnaround in Mins],
    SUM([Bed Turnaround Time]) / MAX(CNT) / 60 AS [AVG Turnaround in Hours],
    SUM([Bed Turnaround Time]) / MAX(CNT) / 60 / 24 AS [AVG Turnaround in Days]
    FROM #TEMP 
    GROUP BY [Year], [Month], Speciality 
    ORDER BY [Year], [Month], Speciality

    Many Thanks
    Thursday, April 04, 2013 12:53 AM

All replies