case and sum is the following bad for performance

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)

case and sum is the following bad for performance

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-06-08 : 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
);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-08 : 16:19:05

why are you using partition by inside case when? can you explain with some sample data what exactly you're trying to achieve?

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

Nader
Starting Member

41 Posts

Posted - 2012-06-08 : 17:47:09

Thank you I found what i was doing wrong.
I found a solution for that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 02:08:33

ok...would you mind posting what final solution is?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources