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:
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:
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:
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]