Click here to Skip to main content
15,072,753 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:
SQL
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
Posted
Updated 3-Dec-11 7:21am
v3
Comments
[no name] 3-Dec-11 20:25pm
   
explain every field you;re trying to retrieve. using case in sql looks something like this:
SELECT status CASE WHEN status = 1 THEN 'active' WHEN status = 2 THEN 'inactive' END
[no name] 3-Dec-11 20:26pm
   
im not sure in using aggregates like SUM and MAX in case titles. review your codes pls.
Member 8371764 6-Dec-11 2:52am
   
select slno,visttime=REPLACE(REPLACE(CONVERT(varchar(15),@visttime,100),'PM',' PM'),'AM',' AM'),doctorcode,REPLACE(REPLACE(CONVERT(varchar(15),visttime,100),'PM',' PM'),'AM',' AM')+':'+CONVERT(nvarchar(11), datee,106)as good ,vistday=case when vistday='1' then 'Sunday' when vistday='2' then 'Monday' when vistday='3' then 'Tuesday' when vistday='4' then 'Wednesday' when vistday='5' then 'Thursday' when vistday='6' then 'Friday' when vistday='7' then 'Saturday' end,pd=case when pd='y' then 'yes' when pd='N' then 'No' end from table-name

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900