Jeff Moden (5/3/2015)

---

Uday3421 (5/3/2015)

---

ID A B C AVG

------------------------

1 08 09 10 -

------------------------

2 10 25 26 -

------------------------

3 09 15 16 -

------------------------

Friends,

I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column,

PLz help me out.......

Thank you.

There is no need for anything sophisticated here. In this case, brute force is the shortest and most likely the fastest. The "+1" thing in the code below is to meet your "round up" requirements without actually using a function. If the columns are of an integer datatype, then this is all integer math as you requested.

UPDATE #TestTable

SET [Avg] = CASE

WHEN A<B AND A<C THEN (B+C+1)/2

WHEN B<A AND B<C THEN (A+C+1)/2

ELSE (A+B+1)/2

END

;

If someone desires to check for performance, here's my normal million row test table.

--===== If the test table exists, drop it to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate the test table on the fly.

SELECT TOP 1000000

ID = IDENTITY(INT,1,1)

,A = ABS(CHECKSUM(NEWID()))%30

,B = ABS(CHECKSUM(NEWID()))%30

,C = ABS(CHECKSUM(NEWID()))%30

,[AVG] = CAST(0 AS INT)

INTO #TestTable

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

;

And, of course, the following query demonstrates that the data provided by the OP is also calculated correctly according to the OP.

--===== Display the answers for given rows.

SELECT tt.*

FROM #TestTable tt

JOIN (

SELECT 08,09,10 UNION ALL

SELECT 10,25,26 UNION ALL

SELECT 09,15,16

)d(A,B,C)

ON tt.A = d.A

AND tt.B = d.B

AND tt.C = d.C

;