Query required for my scnario

  • vs.satheesh

    SSCrazy

    Points: 2215

    Hi , I am using sqlserver 2005, UIn my scenario I need apply gracing marks for following scenario.

    drop table #temptable

    create table #temptable (USN varchar(15),Scode varchar(10),Theory_Mark int,Practical_mark int)

    insert into #temptable values('156101201098' ,'156101054', 68.00, 30.00)

    insert into #temptable values('156101201067', '156101054', 68.00, 29.00)

    insert into #temptable values('156101201086', '156101054', 67.00, 36.00)

    insert into #temptable values('156101201098', '156101054', 68.00, 30.00)

    insert into #temptable values('156101202008', '156101054', 65.00, 30.00)

    insert into #temptable values('156101205020', '156101053', 68.00, 29.00)

    In this above table I want grace mark . Grace mark rules is

    Maximum grace mark for each subject : 5

    Thory pass mark : 70

    Practical Pass Mark : 30

    I need following result

    USN,Scode,Theory_mark,Thory_grace,Practailcal_mark,PracticalGrace.

    156101201098 156101054 68 2 30 0

    156101201067 156101054 68 2 29 0

    156101201086 156101054 67 3 36 0

    156101201098 156101054 68 2 30 0

    156101202008 156101054 65 5 30 0

    156101205020 156101053 68 3 29 1

    156101205021 156101052 70 0 25 5

    Grace Mark should be meet pass mark.But only 5 mark maximum. grace mark split up also required

  • Lowell

    SSC Guru

    Points: 323357

    great job on providing the set up!

    i'm a little unclear on what the rule is for the gracemarks, though;

    if they have a Practical_mark > 30, they get get a 70 score, or they get +5.0 points?

    SELECT

    CASE WHEN Practical_mark >= 30.00

    THEN 70.00 ELSE Theory_Mark

    END AS GraceMark,*

    FROM #temptable

    SELECT

    CASE WHEN Practical_mark >= 30.00

    THEN Theory_Mark + 5.0 ELSE Theory_Mark

    END AS GraceMark,*

    FROM #temptable

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Luis Cazares

    SSC Guru

    Points: 183531

    vs.satheesh (11/29/2016)


    Hi , I am using sqlserver 2005, UIn my scenario I need apply gracing marks for following scenario.

    drop table #temptable

    create table #temptable (USN varchar(15),Scode varchar(10),Theory_Mark int,Practical_mark int)

    insert into #temptable values('156101201098' ,'156101054', 68.00, 30.00)

    insert into #temptable values('156101201067', '156101054', 68.00, 29.00)

    insert into #temptable values('156101201086', '156101054', 67.00, 36.00)

    insert into #temptable values('156101201098', '156101054', 68.00, 30.00)

    insert into #temptable values('156101202008', '156101054', 65.00, 30.00)

    insert into #temptable values('156101205020', '156101053', 68.00, 29.00)

    In this above table I want grace mark . Grace mark rules is

    Maximum grace mark for each subject : 5

    Thory pass mark : 70

    Practical Pass Mark : 30

    I need following result

    USN Scode Theory_mark Theory_grace Practailcal_mark Practical_Grace

    156101201098 156101054 68 2 30 0

    156101201067 156101054 68 2 29 0

    156101201086 156101054 67 3 36 0

    156101201098 156101054 68 2 30 0

    156101202008 156101054 65 5 30 0

    156101205020 156101053 68 3 29 1

    156101205021 156101052 70 0 25 5

    Grace Mark should be meet pass mark.But only 5 mark maximum. grace mark split up also required

    How do you get your last 2 rows from the expected results? The second to last seems wrong and the last is not in the sample data. Otherwise, something like this might help.

    SELECT USN,

    Scode,

    Theory_Mark,

    CASE WHEN Theory_Mark >= 70.00

    THEN 0.00

    WHEN Theory_Mark <= 65.00

    THEN 5.00

    ELSE 70 - Theory_Mark

    END AS Theory_grace,

    Practical_mark,

    CASE WHEN Practical_mark >= 30.00

    THEN 0.00

    WHEN Practical_mark <= 25.00

    THEN 5.00

    ELSE 30 - Practical_mark

    END AS Practical_grace

    FROM #temptable;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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