Quert Required

Question

Quert Required

vs.satheesh

SSCrazy

Points: 2392
More actions
September 14, 2016 at 6:39 am

#313058

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

Here you go. Cross applying the values clause gives you both the difference (df) and final mark (fm) for each combination on a separate row. The row number function then sorts the results by the difference (smallest to largest) and the final mark (largest to smallest) and assigns number 1, 2, 3 within each registerno. Rows with [SortID] = 1 will have the highest final mark for the lowest difference.

with cte as (

select *

from #resultdata

cross apply (Values (ABS(LevelOneMark - LeveltwoMark),(LevelOneMark + LeveltwoMark)/2)

,(ABS(LeveltwoMark - LevelthreeMark),(LeveltwoMark + LevelthreeMark)/2)

,(ABS(LevelOneMark - LevelthreeMark),(LevelOneMark + LevelthreeMark)/2)

) v (Df, fm)

)

,cte2 as (select *,ROW_NUMBER() over(partition by RegisterNo order by df, fm desc) as SortID from cte)

select Registerno, LevelOneMark, LeveltwoMark, LevelthreeMark, fm as FinalMark

from cte2

where SortID = 1

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

http://www.sqlservercentral.com/Forums/FindPost1817587.aspx 😀

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

Great minds?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

The Dixie Flatline (9/14/2016)
Great minds?

"...fools seldom differ" :-D:-D:-D

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

I say CTE... you say DT... let's call the whole thing off!

I should change my example to use nested CASE statements and argue that it avoids blocking.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

vs.satheesh SSCrazy Points: 2392 More actions · Answer 6

Thank you for your reply. But my client using sqlserver2005.This Query not working in sqlserver2005.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

vs.satheesh (9/14/2016)
Thank you for your reply. But my client using sqlserver2005.This Query not working in sqlserver2005.

Please post the helpful error message.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

J Livingston SQL (9/14/2016)
http://www.sqlservercentral.com/Forums/FindPost1817587.aspx 😀

And please don't post in multiple forums. And most especially don't post in forums of a higher level than your production level. That way you won't get solutions using functionality you don't have yet. 🙂

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills