Skip to content
Advertisement

Include Medians for grouped query .

I have this query that I need to graph.
The graph will show Total patients, patient Longest wait (minutes) and patient Median wait for each location. I need to include Medians in my query, and that is where I am stuck.

Example Raw Data:

enter image description here

Query I have so far w/o Medians:

    SELECT 
[Location],
      count([Patient_Number]) Total,
      MAX([WaitTime_Min]) LongWait
     
  FROM MyTable
  where
  [Location] in ('AMB', 'PEDS', 'WALK')
  and [EDNurse] is NULL
  group by [Location]

Output:

enter image description here

I need help getting a last column of Medians for WaitTime (from the raw data) for each location. Any help would be appreciated; Thanks!!

Desired Output:

enter image description here

Advertisement

Answer

There are PERCENTILE_CONT(for continues values), PERCENTILE_DISC(for discrete value) methods in Sqlserver 2012 for doing that. you can read more about it here.

SELECT 
[Location],
      count([Patient_Number]) Total,
      MAX([WaitTime_Min]) LongWait,
     max(MedianDisc) MedianDisc, -- Discrete median
     max(MedianCont) MedianCont -- continues median
  FROM (
          select  m.*, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY WaitTime_Min)   
                            OVER (PARTITION BY [Location]) AS MedianCont  
      ,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY WaitTime_Min)   
                            OVER (PARTITION BY [Location]) AS MedianDisc
            from myTable m
             where
  [Location] in ('AMB', 'PEDS', 'WALK')
  and [EDNurse] is NULL
   
  )tt
 
  group by [Location]

Update: based on performance issues in this method that Aaron commented, I add a pure SQL calculation for Median:

select  [Location], 
        count([Patient_Number]) Total,
        MAX([WaitTime_Min]) LongWait,
        AVG(1.0 * LongWait) As Median
FROM
(
    SELECT [Location],
       [Patient_Number]  Total,
       [WaitTime_Min]  LongWait
        , ra=row_number() over (partition by [Location] order by [WaitTime_Min])
        , rd=row_number() over (partition by [Location] order by [WaitTime_Min] desc)
    from myTable m
    where [Location] in ('AMB', 'PEDS', 'WALK')
      and [EDNurse] is NULL
) as x
where ra between rd-1 and rd + 1
group by [Location]
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement