August 2, 2011 at 7:35 am
Hi everyone
This might be easier in T-SQL or in SRSS itself. I've been asked to calculate the median wait time in one of our datesets. At the moment i calculate the average as
avg(datediff(dd, DATE1, DATE2) as 'Average_Wait_Time'
From what i can gather calculating the median is much more difficult and there isn't a built-in function.
Anybody got any ideas?
Thanks in advance
BTW
PS I'm using SQL/SSRS 2008.
August 2, 2011 at 8:27 am
Think you will have to write custom code within SSRS - below a couple of artricles that should give you a starting point.
http://msdn.microsoft.com/en-us/library/bb395166(v=sql.90).aspx
http://techfilth.blogspot.com/2008/07/calculate-median-on-group-in-ssrs.html
August 2, 2011 at 9:45 am
Cheers
I'll give them a look...
August 4, 2011 at 1:30 pm
Calculating medians in SQL server isn't straightforward.
If you can do the work in T-SQL and just pass the result to RS, this link will help you:
http://www.sqlmag.com/article/tsql3/calculating-the-median-gets-simpler-in-sql-server-2005
Here's code I created based on Ben-Gan's article, running on the Northwind Database. The comments are my own, explaining the logic behind the cleverness.
--Listing 5: Calculating Median Value per Employee by Using a CTE and ROW_NUMBER
--===============================================================================
/*
HOW IT WORKS (Returns median of [Value]), relying on INTeger division truncating remainders:
The CTE's ROW_NUMBER function orders the data in sequence of [Value], partitioned here by EmployeeID, though that's not required.
THE CTE also includes a Count() function for the number of rows per EmployeeID.
Then the main query selects AVG([Value]) as the median, from a list of 2 rows specified by WHERE:
-RowNum = (Cnt + 1)/2
-RowNum = (Cnt + 2)/2
Consider the 2 cases possible - an even or odd number for Cnt:
EVEN (e.g., Cnt=16)
(Cnt + 1)/2 = 8! b/c Cnt is by default an INT and SQL's INT division truncates the remainder of 17/2
(Cnt + 2)/2 = 9.
So the average of the 8th and 9th rows is returned as the median.
ODD (e.g., Cnt=15)
(Cnt + 1)/2 = 8
(Cnt + 2)/2 = 8 (again, INTeger math truncation).
The average of the 8th and 8th rows is returned as the median.
*/
WITH OrdersRN AS
(
SELECT EmployeeID, Value,
ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Value) AS RowNum,
COUNT(*) OVER(PARTITION BY EmployeeID) AS Cnt
FROM dbo.VOrders
)
SELECT EmployeeID, AVG(Value) AS Median
FROM OrdersRN
WHERE RowNum IN((Cnt + 1) / 2, (Cnt + 2) / 2)
GROUP BY EmployeeID;
Rich
August 4, 2011 at 2:41 pm
Cheers for this - i should be able to adapt easily for my sproc...
August 4, 2011 at 3:18 pm
Excellent, glad I could help.
Read everything you can of Itzik Ben-Gan's, you will learn much!
Rich
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy