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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy