May 30, 2012 at 2:00 pm
I want to perform the following operation
I copied the excel spread sheet that has the calculation required for the function
assessmentidGradeStatusmaxpointscorerownumberassessmentcategoryCatWeightdeletelowest
12549G1001001301150
15337G10060130250
15336G10065230250
12548G100853302500
15347G109430250
15339G1010530250
15340G1010630250
15341G1010730250
15342G1010830250
15343G1010930250
15344G10101030250
15345G10101130250
15346G10101230250
12543G100661303401
12542G100702303401
12544G100903303401
2389G100741304401
2360G100852304401
steps
categoryidcatweightTotalScoreTotalMaxPointspercperc =(catweight*totalscore*1.0)/ TotalMaxPoints
30152993903.833333333 else TotalMaxPoints
3021510010015 end)
304408510034
3034016020032
10084.83333333
final percentage=84.83333333final percentage=(SUM( perc)*100.0)/SUM(catweight)
we need to divide by total catweight because may be one of the categories has all null numbers so it will not be included
we are excluding null scores and delete lowest ones.
May 30, 2012 at 2:09 pm
Please here is my function that needs improvement to perform what is mentioned in the excel spreadsheet
Thanks in advance
CREATE FUNCTION [dbo].[fn_UpToNowPercentage_dropL_table]
( @SectionID int,
@StudentID int
)
RETURNS table
AS
return
(
select SUM( (catweight*totalscore*1.0)/(case when TotalMaxPoints =0 then 1
else TotalMaxPoints
end))*100.0/SUM(catweight) as perc1
from
( select catweight,assessmentCategory,
sum(score) as totalscore,sum(
(case when gradestatus='C' then 0
else maxpoint
end)) as totalmaxpoints
from (select
AP.AssessmentID,
GradeStatus,
A.points
as maxpoint,
AP.points as score,
ROW_NUMBER()
over(
partition by studentid,A.sectionid,Assessmentcategory
order by (ap.points*1.0/a.points)) as rownumber ,
assessmentcategory,
CatWeight,
deletelowest
fromGradeBook.Assessment A
inner join
GradeBook.AssessmentPoint AP
on
A.AssessmentID=AP.assessmentid
inner join
GradeBook.GBCategory GC
on
A.AssessmentCategory=GC.CategoryID
where A.SectionID=@sectionid
and studentid=@studentid
and a.points >0
and ap.points is not null
and a.deleted=0
and
ap.points >=0
)S
where S.rownumber >deletelowest
group by assessmentCategory,catweight) T
);
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply