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
assessmentid GradeStatus maxpoint score rownumber assessmentcategory CatWeight deletelowest
12549 G 100 100 1 301 15 0
15337 G 100 60 1 302 5 0
15336 G 100 65 2 302 5 0
12548 G 100 85 3 302 5 0 0
15347 G 10 9 4 302 5 0
15339 G 10 10 5 302 5 0
15340 G 10 10 6 302 5 0
15341 G 10 10 7 302 5 0
15342 G 10 10 8 302 5 0
15343 G 10 10 9 302 5 0
15344 G 10 10 10 302 5 0
15345 G 10 10 11 302 5 0
15346 G 10 10 12 302 5 0
12543 G 100 66 1 303 40 1
12542 G 100 70 2 303 40 1
12544 G 100 90 3 303 40 1
2389 G 100 74 1 304 40 1
2360 G 100 85 2 304 40 1
steps
categoryid catweight TotalScore TotalMaxPoints perc perc =(catweight*totalscore*1.0)/ TotalMaxPoints
301 5 299 390 3.833333333 else TotalMaxPoints
302 15 100 100 15 end)
304 40 85 100 34
303 40 160 200 32
100 84.83333333
final percentage= 84.83333333 final 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
from GradeBook.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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply