Uday3421 (5/3/2015)
ID A B C AVG------------------------
1 08 09 10 10
------------------------
2 10 25 26 26
------------------------
3 09 15 16 16
------------------------
i trying to get the average of two largest number from the column a,b&c for the particular identity and store it into he average column...
Like,
for ID=1
the two largest numbers are 10 and 09 and the average of that two number is (10+9)/2=10.
(25+26)/2=26.
and
(15+16)/2=16.
the result should not come in the float number......
Didn't see this post before I posted the previous code, here is an adjustment to it according to these requirements.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TAVG TABLE
(
ID INT NOT NULL
,A INT NOT NULL
,B INT NOT NULL
,C INT NOT NULL
,[AVG] INT NULL
);
INSERT INTO @TAVG(ID,A,B,C)
VALUES
(1,08,09,10)
,(2,10,25,26)
,(3,09,15,16);
;WITH BASE_DATA AS
(
SELECT
A.ID
,ROW_NUMBER() OVER
(
PARTITION BY A.ID
ORDER BY X.VAL DESC
) AS X_DRID
,X.VAL
FROM @TAVG A
CROSS APPLY
(
SELECT A.A UNION ALL
SELECT A.B UNION ALL
SELECT A.C
) AS X(VAL)
)
,AVG_DATA AS
(
SELECT
BD.ID
,CEILING(SUM(BD.VAL) / 2.0) AS AVG_VAL
FROM BASE_DATA BD
WHERE BD.X_DRID < 3
GROUP BY BD.ID
)
UPDATE TA
SET TA.[AVG] = AD.AVG_VAL
FROM @TAVG TA
INNER JOIN AVG_DATA AD
ON TA.ID = AD.ID
;
SELECT
A.ID
,A.A
,A.B
,A.C
,A.[AVG]
FROM @TAVG A;
Results
ID A B C AVG
----------- ----------- ----------- ----------- -----------
1 8 9 10 10
2 10 25 26 26
3 9 15 16 16