quote:

---

Originally posted by russell

have a look here http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/9ff324f6-854c-4a39-9e8b-0ab9d3151558

both Adam Mechanic and Itzik Ben-Gan have solutions for Median

---

Create Function SiteStats.Median (@day int, @year int)
Returns decimal (9, 3)
AS
BEGIN
	Declare @Median decimal (9, 3)

	Declare @h1 bigint
	Declare @h2 bigint

	SELECT @h1 = MAX(totalcount) FROM (
		SELECT	TOP 50 PERCENT totalcount
		FROM	SiteStats.OnlineNowHistory
		WHERE	DatePart(dy, [Time]) = @day
		And	DatePart(yy, [Time]) = @year
		ORDER BY totalcount
	) h1

	SELECT	@h2 = MIN(totalcount) FROM (
		SELECT	TOP 50 PERCENT totalcount
		FROM	SiteStats.OnlineNowHistory
		WHERE	DatePart(dy, [Time]) = @day
		And	DatePart(yy, [Time]) = @year
		ORDER BY totalcount DESC
	) h2

	SELECT @Median = (@H1 + @H2) / 2.0
	
	Return @Median
END

select	datePart(dy,s.Time) as theday, DATEPART(YY,s.Time) as theyear,
	AVG(cast(s.TotalCount as bigint)) as dayaverage, 
	MIN(cast(s.TotalCount as bigint)) as daymin,
	MAX(cast(s.TotalCount as bigint)) as daymax,
	SiteStats.median(datePart(dy,s.Time), DATEPART(YY,s.Time)) as daymedian
from	SiteStats.OnlineNowHistory as s
where	s.Time > '2010-6-19 00:00:00'
and	s.Type = 1 --type 1 myspace type 2 instant messenger
and	s.TotalCount >= 1
group by
	datePart(dy,s.Time), datePart(yy,s.Time)