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_Grace15610120109815610105468 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;