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