My function that need improvement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

My function that need improvement

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-05-30 : 16:07:39

Here is my function that I need to improve to perform what is mentioned in the excel spreadsheet.
Thanks

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

);

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-30 : 16:10:38

you question is unclear

what do you want to improve?
Also what do you mean by what is mentioned in the excel spreadsheet?
how do you suppose anyone to know whats specified in your excel sheet which we dont have access to ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources