Query Required

  • vs.satheesh

    SSCrazy

    Points: 2215

    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

  • 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: 2215

    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)

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