September 14, 2016 at 1:53 am
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
Please help me
September 14, 2016 at 6:58 am
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
September 14, 2016 at 10:44 pm
Thank You for replay.My client using SQL server 2005 . This Query not working sqlserver 2005.
September 15, 2016 at 4:13 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply