Hi

Thought I would have a go at this. I've used a CTE to order and number the results by course year.

I've also created the statistics in separate queries to try and make it a be easier to manage and read.

To determine the medians I have used rounding to determine low and high row number for each median. These can be the same number. There is probably a nicer way to do this:-)

I've left the columns in the queries that I was using to validate my median choices.

They are all joined together in the final query. I have also added a Geometry to visualize it.

;with cte as (

select ROW_NUMBER() OVER (PARTITION BY year(CourseDate) ORDER BY GRADE) RN,

COUNT(*) OVER (PARTITION BY year(CourseDate)) C,

ROUND(((COUNT(*) OVER (PARTITION BY year(CourseDate)) + 1) / 2.0) - .1, 0) HC1,

ROUND(((COUNT(*) OVER (PARTITION BY year(CourseDate)) + 1) / 2.0) + .1, 0) HC2,

year(CourseDate) CourseYear,

CAST(Grade AS NUMERIC(3)) Grade

from EStats

)

,minmax as (

select courseYear, min(Grade) minGrade, max(Grade) maxGrade, min(c) c

from cte

group by courseYear

)

,median as (

select courseYear, avg(grade) medianGrade

, cast(min(hc1) as int) hc1, cast(min(hc2) as int) hc2

from cte

where rn in (hc1, hc2)

group by courseYear

)

,lowQtr as (

select courseyear, avg(grade) lowQtrGrade

,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0)) as int) l1,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0)) as int) l2

from cte

where rn in (round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0),round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0))

group by courseyear

)

,highQtr as (

select courseyear, avg(grade) highQtrGrade

,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0) + hc1) as int) h1,cast(min(round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0) + hc1) as int) h2

from cte

where rn in (round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 - .1,0) + hc1,round((hc1 - 1 + abs(hc2 - hc1) + 1) / 2.0 + .1,0) + hc1)

group by courseyear

)

select mm.courseYear,

cast(md.medianGrade as numeric(5,2)) Median,

cast(mm.maxGrade as numeric(5,2)) Max,

cast(mm.minGrade as numeric(5,2)) Min,

cast(lq.lowQtrGrade as numeric(5,2)) LowerQ,

cast(hq.highQtrGrade as numeric(5,2)) UpperQ,

Geometry::STGeomFromText(

'MULTILINESTRING((' +

cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- minTick

cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- maxTick

cast(cast(medianGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(medianGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- medianTick

cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- lowerTick

cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + '),(' + -- upperTick

cast(cast(minGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 as varchar(5)) + ',' + cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 as varchar(5)) + '),(' + -- lowWhisker

cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 as varchar(5)) + ',' + cast(cast(maxGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 as varchar(5)) + '),(' + -- highWhisker

cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 - 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 - 2.5 as varchar(5)) + '),(' + -- box1

cast(cast(lowQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric(5)) - 2000) * 10 + 2.5 as varchar(5)) + ',' + cast(cast(highQtrGrade as numeric(5,2)) as varchar(5)) + ' ' + cast((cast(mm.courseyear as numeric) - 2000) * 10 + 2.5 as varchar(5)) + -- box2

+ '))'

,0) graph

from minmax mm

inner join median md on md.courseyear = mm.courseyear

inner join lowQtr lq on lq.courseyear = mm.courseyear

inner join highQtr hq on hq.courseyear = mm.courseyear