|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 06/08/2012 : 15:35:49
|
i want to know how may are A and how many are P and how many are F
I can not use pivot because this a function called for each section in another bigger stored procedure
USE [SP3]
GO
/****** Object: UserDefinedFunction [dbo].[fn_NewSLO_SectionSLO_Achieve_bySLO_Inline] Script Date: 06/08/2012 11:40:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_NewSLO_SectionSLO_Achieve_bySLO_Inline]
(@SectionID INT,
@SLOID int,
@FullyAchieved int,
@PartiallyAchieved int) RETURNS TABLE
AS RETURN (
select sum(T.Fully)as
'FullyAchieved',
sum (T.Partially) as 'PartiallyAchieved',
sum(T.Failed) as 'Failedtoachieve'
from
(SELECT
e.StudentID,
(case when
sum ((isnull(AP.Points,0)*1.0/a.points)*asl.[weight])
over (partition by e.studentid) >= @fullyachieved
then 1 else 0 end) as 'Fully',
(case
when
sum ((isnull(AP.Points,0)*1.0/a.points)*asl.[weight])
over (partition by e.studentid) < @fullyachieved
and
sum ((isnull(AP.Points,0)*1.0/a.points)*asl.[weight])
over (partition by e.studentid) >= @partiallyAchieved
then 1 else 0 end) as 'Partially',
(case when
sum ((isnull(AP.Points,0)*1.0/a.points)*asl.[weight])
over (partition by e.studentid) < @Partiallyachieved
then 1 else 0 end) as 'Failed'
FROM GradeBook.AssessmentPoint ap
inner join
gradebook.assessment a
on
ap.assessmentid=a.assessmentid
inner join
slo.assessmentslo asl
on
asl.assessmentid=a.assessmentid
inner join
dbo.fn_Enrolled_Parent_Child_inline (@sectionid) e
on
ap.StudentID=e.studentid
where
asl.sloid=@sloid
and
asl.sloid=@sloid
and
a.deleted=0
and a.points >0
and [weight]>0
)T
);
|
Edited by - sarahmfr on 06/08/2012 15:56:52
|
|
case and sum is the following bad for performance
