Taking median of calculation in SQL Server

MyTable in SQL Server contains _TimeStamp, Column1, Column2, and Column3, which have the following values:

_TimeStamp Column1 Column2 Column3  
'2010-10-11 15:55:25.40' 10  3  0.5  
'2010-10-11 15:55:25.50' 20  9  0.7  
'2010-10-11 15:55:25.60' 15  2  1.3  
'2010-10-11 15:55:25.70' 17  8  2.7  
'2010-10-11 15:55:25.80' 42  6  3.6  
'2010-10-11 15:55:25.90' 14  2  0.4  

I would like to find the median value of 4*Column1*Column2*Column3. The products for each row are:

开发者_StackOverflow社区 60

504

156

1468.8

3628.8

44.8

Since there are an even number of entries, the median value is the average of the two 'middle' entries (156 and 504), or 330. Also I only want to perform the median on values that fall within a chosen time range, and the solution can't alter the database which isn't mine. I edited a similar query to get the query shown below, but it fails because a column name is invalid. Any suggestions?

SELECT
  AVG(4*Column1*Column2*Column3)
  FROM 
(
  SELECT
    4*Column1*Column2*Column3,
    ROW_NUMBER() OVER (
    ORDER BY 4*Column1*Column2*Column3 ASC, _TimeStamp ASC) AS RowAsc,
    ROW_NUMBER() OVER (
    ORDER BY 4*Column1*Column2*Column3 DESC, _TimeStamp DESC) AS RowDesc
  FROM MyTable WHERE 
   _TimeStamp BETWEEN '2010-10-11 15:55:25.40' AND '2010-10-11 15:55:25.90' 

) x
WHERE 
  RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

I think all you need to do to your candidate statement is to alias the computed value in the subquery, then ask for the AVG of that alias in the outer query:

SELECT
  AVG(MyValue)
  FROM 
(
  SELECT
    4*Column1*Column2*Column3 AS MyValue,
    ROW_NUMBER() OVER (
    ORDER BY 4*Column1*Column2*Column3 ASC, _TimeStamp ASC) AS RowAsc,
    ROW_NUMBER() OVER (
    ORDER BY 4*Column1*Column2*Column3 DESC, _TimeStamp DESC) AS RowDesc
  FROM MyTable WHERE 
   _TimeStamp BETWEEN '2010-10-11 15:55:25.40' AND '2010-10-11 15:55:25.90' 

) x
WHERE 
  RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)

I haven't tested this though.