Specifying achievement level per dynamic levels pe

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)

Specifying achievement level per dynamic levels pe

Author

Topic

Nader
Starting Member

41 Posts

Posted - 2012-06-14 : 13:06:33

I am trying to get the percentage of students who lie in a particular category of achievement level according to their total percenatage the categories varies in number,naming, and values
I had the following function because we had specifically 3 categories and exact percentage now those become dynamic
how can I rewrite the following function (best performance) this will be called on an organizational level
CREATE FUNCTION [dbo].[fn_NewSLO_SectionSLO_Achieve_bySLO_Inline_try]
(@SectionID INT,
@FullyAchieved int,
@PartiallyAchieved int) RETURNS TABLE
AS RETURN (
select count(case when T.Perc >= @FullyAchieved then T.StudentID end) as [FullyAchieved],
count(case
when (T.Perc < @FullyAchieved and T.Perc >=@partiallyAchieved)
then T.StudentID
end)
as [PartiallyAchieved],
count(case when T.Perc < @partiallyAchieved then T.StudentID end) as [FailedAchieved]

from
(SELECT
e.StudentID as StudentID,
sum ((isnull(AP.Points,0)*1.0/a.points))
as Perc

FROM GradeBook.AssessmentPoint ap
inner join
gradebook.assessment a
on
ap.assessmentid=a.assessmentid)T
);
Now the level of achievement became dynamic
i.e. I do not have @fullyachieved,@partiallyAchieved percentage
I have now a table
Orgid, achievementlevelname, percentage
1,Beginning,60
1, developing,70
1, meet expectations,80
1,exceedsexpectations,90
2, fullyachieved, 90
2,partiallyachieved,80
2,partiallyachieved,60

Each organization will have a different number of achievementlevels and different percentage
What is the best way to get the previous working with the new dynamic achievement levels and percentages

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 15:34:20

add table as a join to your current query and use field from table rather than variable @FullyAchieved ,@partiallyAchieved etc in case when. also you might have to add an extra filter on orgid

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources