Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a SPROC which calculates the Mean duration of a request / response cycle within 10 minute intervals. This works well and suits my needs for plotting charts. What I would like to do next is calculate the Median value... I suspect I would need a subquery but can't figure out how to accomplish this.

SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time]
     ,AVG(CASE WHEN Duration is null OR Duration = 0 THEN null ELSE Duration END) AS [Mean Response Time]
FROM [Application].[Exchange] WITH (NOLOCK)
WHERE [Started] >= '24 Oct 2012' AND [Started] < '25 Oct 2012'
GROUP BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) 
ORDER BY dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0)  

Can anyone suggest a way I might accomplish this?

share|improve this question
    
CLR aggregate function. Which version of SQL Server are you using? –  Jon Seigel Oct 25 '12 at 14:06
    
SQL Server 2008 and SQL Azure –  JoeGeeky Oct 25 '12 at 14:13
2  
Two standard methods due to Itzik Ben-Gan and Joe Celko are shown in this blog post by Adam Machanic. –  Paul White Oct 26 '12 at 13:33

1 Answer 1

up vote 2 down vote accepted
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- replace WITH (nolock)

SELECT [Time]
       ,AVG(NULLIF(Duration,0)) AS [Mean Response Time]
       ,AVG(CASE WHEN Rn in (Round(CN/2.0,0),Round((CN+1)/2.0,0))
                 THEN Duration END) [Median]
FROM
(
  SELECT [Time], Duration,
         Rn = Row_Number() over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time
                                 order by Duration),
         Cn = Count(1) over (partition by CASE WHEN NullIf(Duration,0) is null then 1 else 2 end,
                                         Time)
  FROM (
    SELECT dateadd(minute, 10 + (datediff(minute, 0, [Started]) / 10) * 10, 0) AS [Time],
           Duration
    FROM [Application].[Exchange]
    WHERE [Started] >= '20121024' AND [Started] < '20121025'
  ) X
) Y
GROUP BY [Time]
ORDER BY [Time];
share|improve this answer
3  
+1 but your answer will be more useful if you use yyyymmdd date literals such as '20121024' instead of '24 Oct 2012', because they will work for non-English speaking locales. –  AlexKuznetsov Oct 25 '12 at 14:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.