hello frnds,
I've a stored procedure query in sql server.
i want to calculate Grade from percentage using case
in SP.
but when i m using "case" with "when" then only first condition
is calculating not other rest case condition.
Percentage and Grade fields are not storing in database.
I want print report using this SP at .aspx web form
my code is:
ALTER proc [dbo].[spGetStudentFormative]
@intStudentID int,
@intMasterClassID int
as
begin
select
intSubjectId
,max(case when strEvaluationTerm = 'Evaluation I ' then strSubjectName end) Subjects
,sum(case when strEvaluationTerm = 'Evaluation I' then decMaxMarks end) Out_of
,sum(case when strEvaluationTerm = 'Evaluation I' then decObtainedMarks end) Marks_of_Skills
,sum(case when strEvaluationTerm = 'Evaluation I' then decPercentage end) Percentage
,max(case when strEvaluationTerm = 'Evaluation I' then strGrade end) Grade
,sum(case when strEvaluationTerm = 'Evaluation II' then decMaxMarks end) Out_of
,sum(case when strEvaluationTerm = 'Evaluation II' then decObtainedMarks end) Marks_of_Skills
,sum(case when strEvaluationTerm = 'Evaluation II' then decPercentage end) Percentage
,max(case when strEvaluationTerm = 'Evaluation II' then strGrade end) Grade
,sum(case when strEvaluationTerm = 'Evaluation III' then decMaxMarks end) Out_of
,sum(case when strEvaluationTerm = 'Evaluation III' then decObtainedMarks end) Marks_of_Skills
,sum(case when strEvaluationTerm = 'Evaluation III' then decPercentage end) Percentage
,max(case when strEvaluationTerm = 'Evaluation III' then strGrade end) Grade
,sum(case when strEvaluationTerm = 'Evaluation I' or strEvaluationTerm = 'Evaluation II' or strEvaluationTerm = 'Evaluation III' then decMaxMarks end) Out_of
,sum(case when strEvaluationTerm = 'Evaluation I' or strEvaluationTerm = 'Evaluation II' or strEvaluationTerm = 'Evaluation III' then decObtainedMarks end) Total_of_Formatives
,sum(case when strEvaluationTerm = 'Evaluation I' or strEvaluationTerm = 'Evaluation II' or strEvaluationTerm = 'Evaluation III' then decPercentage/3 end) Percentage
,max(case when strEvaluationTerm = 'Evaluation I' or strEvaluationTerm = 'Evaluation II' or strEvaluationTerm = 'Evaluation III' then
(case when decPercentage>=91 and decPercentage<=100 then'A1'
when decPercentage>=81 and decPercentage <=90 then'A2'
when decPercentage>=71 and decPercentage <=80 then'B1'
when decPercentage>=61 and decPercentage <=70 then'B2'
when decPercentage>=51 and decPercentage <=60 then'C1'
when decPercentage>=41 and decPercentage <=50 then'C2'
when decPercentage>=33 and decPercentage <=40 then'D'
when decPercentage>=21 and decPercentage <=32 then'E1'
else 'E2'end)end)Grade
from vwStudentMarksStructure where (intMasterClassID=@intMasterClassID and intStudentID=@intStudentID)and intCategoryID in( select intCategoryID from vwStudentMarksStructure where (intMasterClassID=@intMasterClassID and intStudentID=@intStudentID)and strDescription='Formative Assessment')
group by intSubjectID
end
Plz help me out!!!!
Thanx in advance