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:
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)