Query required for my scnario

Question

Post reply

Query required for my scnario

vs.satheesh

SSCrazy

Points: 2392
More actions
November 29, 2016 at 6:37 am

#104307

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.
156101201098156101054682300
156101201067156101054682290
156101201086156101054673360
156101201098156101054682300
156101202008156101054655300
156101205020156101053683291
156101205021156101052700255
Grace Mark should be meet pass mark.But only 5 mark maximum. grace mark split up also required

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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: 183706 More actions · Answer 2

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
15610120109815610105468 2 30 0
15610120106715610105468 2 29 0
15610120108615610105467 3 36 0
15610120109815610105468 2 30 0
15610120200815610105465 5 30 0
15610120502015610105368 3 29 1
15610120502115610105270 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