# Query Required

• vs.satheesh

SSCrazy

Points: 2217

Dear All

I want Query for following scenario. In My table we are storing each student three level of marks. I want to find out final marks.

Create table #ResultData (Registerno varchar(12), LevelOneMark int,LeveltwoMark int,LevelthreeMark int)

insert into #ResultData values ( 'R1',80,20,86)

insert into #ResultData values ( 'R2',10,20,86)

insert into #ResultData values ( 'R3',10,20,30)

In this Above table I want to find final mark of each student. Based on least difference I want to find final mark

Scrio 1: (a-b,b-c,c-a)

80-20=60

20-86=66

86-80=6

In this above scenario least difference is 6 . So I want to final result (86+80)/2=83

Scrio 2: (a-b,b-c,c-a)

10-20=10

20-86=66

86-10=76

In this above scenario least difference is 10. So I want to final result (10+20)/2=15

Scrio 3: (a-b,b-c,c-a)

10-20=10

20-30=10

30-10=20

In this above scenario least difference is 10.But difference 10 is two category. In this condition I want to go student favour.

So I want to final result (20+30)/2=25.

I want Result for following result

Registerno, LevelOneMark, LeveltwoMark, LevelthreeMark , FinalMark

R1, 80, 20, 86,83

R2, 10, 20, 86,15

R3, 10, 20, 30,25

• J Livingston SQL

SSC Guru

Points: 51272

maybe....?

`SELECT registerno,`

``` LevelOneMark, LeveltwoMark, LevelthreeMark, FinalMark FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY registerno ORDER BY avalue ASC, FinalMark DESC) rn FROM ( SELECT registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, x.avalue, x.FinalMark FROM ResultData CROSS APPLY(VALUES (ABS(LevelOneMark - LeveltwoMark), (LevelOneMark + LeveltwoMark) / 2 ), (ABS(LeveltwoMark - LevelthreeMark),(LeveltwoMark + LevelthreeMark) / 2 ), (ABS(LevelthreeMark - LeveloneMark), (LevelthreeMark + LeveloneMark) / 2)) x(avalue, FinalMark) ) y ) z ```

`WHERE rn = 1;`

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

• vs.satheesh

SSCrazy

Points: 2217

Thank You for replay.My client using SQL server 2005 . This Query not working sqlserver 2005.

• J Livingston SQL

SSC Guru

Points: 51272

vs.satheesh (9/14/2016)

Thank You for replay.My client using SQL server 2005 . This Query not working sqlserver 2005.

would have been useful if you had posted the error message.....heyho.

try this instead of the "CROSS APPLY(VALUES....." which I think is causing the error

``` SELECT registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, FinalMark FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY registerno ORDER BY avalue ASC, FinalMark DESC) rn FROM ( SELECT registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, ABS(LevelOneMark - LeveltwoMark) AS avalue, (LevelOneMark + LeveltwoMark) / 2 AS FinalMark FROM ResultData UNION ALL SELECT registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, ABS(LeveltwoMark - LevelthreeMark) AS avalue, (LeveltwoMark + LevelthreeMark) / 2 AS FinalMark FROM ResultData UNION ALL SELECT registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, ABS(LevelthreeMark - LeveloneMark) AS avalue, (LevelthreeMark + LeveloneMark) / 2 AS FinalMark FROM ResultData) x ) y WHERE rn = 1; ```

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Viewing 4 posts - 1 through 4 (of 4 total)