|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 04/29/2012 : 18:03:41
|
Please I need help
tables
assessment (assessmentid,sectionid,assessmentitle,maxpoints)
assessmentpoint(assessmentid,studentid,score)
students(studentid,studentnumber,name)
some of the scores is null (not set yet)
I need to add a total column to the pivot table that adds only no null scores for each student in all assessments for that section
currently I am using a udf which slows down the performance
Declare @Sectionid int =3333
DECLARE @Assessments nvarchar(3000)
SELECT @Assessments =
STUFF(
(
select ',[_' + cast(AssessmentID as nvarchar(15))
+ ']'
from GradeBook.Assessment as A
where SectionID=@SectionID
and A.Deleted=0
for xml path('')
),
1,1,''
)
DECLARE @mySQL nvarchar(4000)
declare @ParamDefinition nvarchar(50)
SELECT @mySQL =N'SELECT AP.Points, Ap.StudentID,UA.StudentNumber as ID,
UA.Name as displayname,dbo.fn_UpToNowTotal(@SectionID,AP.StudentID)as Total ,'+
'''_'''+
'+ cast(A.AssessmentID as nvarchar(15)) as Assessment
FROM GradeBook.AssessmentPoint AS AP
INNER JOIN
GradeBook.Assessment AS A
ON
A.AssessmentID = AP.AssessmentID
INNER JOIN
SP.[students] UA
ON
AP.StudentID = UA.Studentid
) Data
PIVOT (
sum(Points)
FOR Assessment
IN (
' + @Assessments + '
)
) PivotTable
order by displayname'
Set @ParamDefinition = '@SectionID int'
/* Execute the Transact-SQL String with all parameter value
Using sp_executesql Command */
Execute sp_Executesql @mySql,
@ParamDefinition,
@
sarah |
|
add a total to pivot table
