--I'm new to T-SQL and one frustration is that aggregate mathematical functions only take tables as arguments, and that they only operate vertically on a single column. This makes it very difficult to use the functions in a formula within a T-SQL statement and even to create a column that uses the functions over a range of other columns.
--For example, it appears to be very difficult to set a new columnD = AVG(columnA,columnB,columnC)
--I've attempted to do this utilizing derived tables with correlated subqueries, but the subqueries appear to loose their correlation, or at least they are correlating according to a rule that I can not discern.
--This is a simplified version of a problem I'm working on. The procedure ranks an initial distribution of values, then determines where a second set of values falls in this distribution, but the real question is how to use the AVG function over multiple columns in the most efficient manner.

--the first part of this code works fine

SET NOCOUNT ON

IF (OBJECT_ID('testrank') IS NOT NULL)
DROP TABLE testrank
GO

CREATE TABLE testrank
(
rowID INT IDENTITY NOT NULL PRIMARY KEY NONCLUSTERED ,
col1 decimal(28,9) null,
col2 decimal(28,9) null,
ranked INT NULL Default 0,
)


-- Seed the table with 5 rows
INSERT testrank (col1) values (1)
INSERT testrank (col1) values (3)
INSERT testrank (col1) values (5)
INSERT testrank (col1) values (7)
INSERT testrank (col1) values (9)
INSERT testrank (col1) values (10)

--rank initital 5 rows
IF (OBJECT_ID('tempdb..#count') IS NOT NULL)
DROP TABLE #count
GO

select rowID=0, col2
into #count
from testrank
where 1=2

insert #count (rowID, col2)
SELECT
rowID=0,
col2=
(SELECT COUNT(tx1.col1) from testrank tx1 where isnumeric(tx1.col1)=1)

update tx1
set
tx1.col2 = (((SELECT COUNT(tx2.col1) from testrank tx2 WHERE tx1.col1 > tx2.col1)+1+(SELECT COUNT(tx2.col1)/2 from testrank tx2 WHERE tx1.col1 = tx2.col1))/(select top 1 tz2.col2 from #count tz2)*100),
tx1.ranked = 1
from testrank tx1

select * from testrank
select 'initial values are ranked correctly, add 5 rows'

INSERT testrank (col1) values (1)
INSERT testrank (col1) values (4)
INSERT testrank (col1) values (5)
INSERT testrank (col1) values (8)
INSERT testrank (col1) values (10)

--find rank relative to previously ranked items

select tx1.rowID, tx1.col1, TestValueGreater =
(
select avg(ta2.col2)
from
(select
top 1
tx2.col2
from testrank tx2
where tx1.col1 <= tx2.col1 and tx2.ranked = 1
order by tx2.col2 asc
)
ta2
)
from testrank tx1
where tx1.ranked = 0 --and tx1.rowID = 5
select 'correctly identifies value equal to or immediately above new value'

select tx1.rowID, tx1.col1, TestValueLess =
(
select avg(ta2.col2)
from
(
select
top 1
tx2.col2
from testrank tx2
where tx1.col1 >= tx2.col1 and tx2.ranked = 1
order by tx2.col2 desc)
ta2
)
from testrank tx1
where tx1.ranked = 0 --and tx1.rowID = 5
select 'correctly identifies value equal to or immediately below new value'

select tx1.rowID, tx1.col1, TestValueAvg =
(
select avg(ta2.col2)
from
((select
top 1
tx2.col2
from testrank tx2
where tx1.col1 <= tx2.col1 and tx2.ranked = 1
order by tx2.col2 asc)
union all
(select
top 1
tx2.col2
from testrank tx2
where tx1.col1 >= tx2.col1 and tx2.ranked = 1
order by tx2.col2 desc))
ta2
)
from testrank tx1
where tx1.ranked = 0
select 'should average prior two values, a rule to account for all of these results eludes me'


select ta1.rowID, avg(TestV1) TestValueAvg2
from
(
select tx1.rowID, tx1.col1, TestV1 =
(select
top 1
tx2.col2
from testrank tx2
where tx1.col1 <= tx2.col1 and tx2.ranked = 1
order by tx2.col2 asc
)
from testrank tx1
where tx1.ranked = 0

union all

select tx1.rowID, tx1.col1, TestV1 =
(
select
top 1
tx2.col2
from testrank tx2
where tx1.col1 >= tx2.col1 and tx2.ranked = 1
order by tx2.col2 desc)
from testrank tx1
where tx1.ranked = 0
) ta1
group by ta1.rowID
select '30% more code works, but excludes the actual values being ranked from the result set'