select cast(avg(cast(datetime_field as numeric)) as datetime)
       from (
		select datetime_field,
		count(*)over( ) total,
		cast(count(*)over( ) as numeric)/2 mid,
		ceiling(cast(count(*)over( ) as numeric)/2) next,
		row_number()over( order by datetime_field) rn
       from table
where datetime_field is not null
		) x
where( total%2 = 0
		and rn in ( mid, mid+1 )
		)
	or  ( total%2 = 1
		and rn = next
		)

select cast(avg(cast(<FIELD_NAME> as float)) as datetime)
       from (
		select <FIELD_NAME>,
		count(*)over( ) total,
		cast(count(*)over( ) as float)/2 mid,
		ceiling(cast(count(*)over( ) as float)/2) next,
		row_number()over( order by <FIELD_NAME>) rn
       from <TABLE_NAME>
where <FIELD_NAME> is not null
		) x
where( total%2 = 0
		and rn in ( mid, mid+1 )
		)
	or  ( total%2 = 1
		and rn = next
		)

quote:

---

Originally posted by Michael Valentine Jones

It would be better if you started with an explanation of exactly what you are are trying to do. Some sample data and expected results would be helpful also:
Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Also, the code you posted appears to be SQL 2005, but you posted on the Transact-SQL (2000) forum. Which are you using?



CODO ERGO SUM

---

declare @t table(
val int
);

insert into @t values (1);
insert into @t values (1);
insert into @t values (1);
insert into @t values (2);
insert into @t values (3);
insert into @t values (4);
insert into @t values (5);
insert into @t values (6);
insert into @t values (7);

select avg(1. * val) as median from
(select row_number() over (order by val asc) as sort_asc
	, row_number() over (order by val desc) as sort_desc
	, val
	from @t) t
	where abs(sort_asc - sort_desc) <= 1

quote:

---

Originally posted by Jeff Moden

Ummm... Median IS the "row in the middle"... are you looking for "Average" instead?

--Jeff Moden

---

asc desc diff 
1   4    3  -- even number of datapoints
2   3    1
3   2   -1
4   1   -3

1   3    2  -- odd number of datapoints
2   2    0
3   1   -2

select avg(val) as median from
(select row_number() over (order by val asc) as sort_asc
	, row_number() over (order by val desc) as sort_desc
	, val
	from table1) as t
	where sort_asc - sort_desc between -1 and 1