Get median value of a time period in SQL Server 2005, 2008 server -

i have table count used licenses every minute. cron task checks session panel , counts number of session every minute. fills session_counts table.

session_counts:

create table [dbo].[session_counts](     [id] [int] identity(1,1) not null,     [license_count] [int] not null,     [created_at] [datetime] not null,     [updated_at] [datetime] not null, primary key clustered  (     [id] asc ) 

example values (i have 525600 rows year in real data table):

id  license_count   created_at      updated_at 1   879     2014-12-30 15:58:42.000 2014-12-30 16:10:50.000 2   55      2014-05-28 12:20:00.000 2014-05-28 12:26:45.000 3   40      2014-03-15 15:51:26.000 2014-03-15 16:02:29.000 4   979     2014-04-25 09:09:49.000 2014-04-25 09:24:03.000 5   614     2014-12-27 11:17:53.000 2014-12-27 11:19:36.000 6   721     2014-09-02 15:20:40.000 2014-09-02 15:32:47.000 7   625     2014-02-27 11:17:42.000 2014-02-27 11:23:26.000 8   1576        2014-12-30 17:18:32.000 2014-12-30 17:36:55.000 9   893     2014-07-26 17:32:47.000 2014-07-26 17:34:22.000 10  897     2014-07-19 11:26:32.000 2014-07-19 11:42:26.000 

now want create statistics between time range this:

select      min(license_count) min_lic   , max(license_count) max_lic   , avg(license_count) avg_lic   , datepart(month, created_at) monat session_counts created_at between '01.01.2014' , '31.12.2014' group datepart(month, created_at) order monat desc 

this gives me min, avg, max usages.

instead of avg want median value of license usage.

my intention cut off min/max peaks in year more real usage statistic. if necessary can setup min/max gap value, if helps.

i checked link function calculate median in sql server before, wasn't able transform need.

i need make run on sql server 2005, 2008 , 2012, can't use percentile_cont() 2012.

if want create table , fill lots of data can provide small tsql fill data:

declare @numrows int,@i int, @date datetime set @numrows = 100000 set @i=1  while @i<@numrows begin   set @date = dateadd(second, rand()*36000, dateadd(day, rand()*365, '2014-01-01 08:00:00'))     insert session_counts (license_count, created_at, updated_at)   select cast(rand()*1000 int) license_count    , @date created_at    , dateadd(second, rand()*1440, @date) updated_at     set @i=@i+1 end 

hope has proper solution problem.

just tried implement method described here: http://www.sqlperformance.com/2012/08/t-sql-queries/median

select min(license_count) min_lic     , max(license_count) max_lic     , avg(license_count) avg_lic     , sum(case when ra between rd - 1 , rd + 1 license_count else 0 end) * 1.0     / sum(case when ra between rd - 1 , rd + 1 1 else 0 end) median_lic     , datepart(month, created_at) monat (     select created_at         , license_count         , row_number() on (partition datepart(month, created_at) order license_count, created_at) ra         , row_number() on (partition datepart(month, created_at) order license_count desc, created_at desc) rd     session_counts     created_at between '01.01.2014' , '31.12.2014' ) t group datepart(month, created_at) order monat desc