How could I get the final average with my type and grade fields out of my evaluation table?

Question

How could I get the final average with my type and grade fields out of my evaluation table?

I am doing a Stored Procedure which asks me as parameters to Id_Estudiante and Id_Curso and return me the Final average by weighting the types of assessments. I've tried it about 3 times and nothing kicks me.

I should note:

20%(PC1) + 20%(PC2) + 30%(TF) + 30%(EB)

My Stored Procedure is as follows:

Create Proc Nota_Final (
    @id_estudiante Char(4),
    @id_curso Char(3))
As
    Select c.Id_Curso , e.Id_Estudiante , Nota_Final = (Select Nota * 0.20 from Evaluacion where Tipo ='PC1') + (Select Nota * 0.20 from Evaluacion where Tipo ='PC2') 
    + (Select Nota * 0.30 from Evaluacion where Tipo ='TF') + (Select Nota * 0.30 from Evaluacion where Tipo ='EB')
    from Evaluacion e inner join Curso c
    on e.Id_Curso = c.Id_Curso 
    inner join Estudiante_Curso ec
    on e.Id_Estudiante = ec.Id_Estudiante 
    where e.Id_Estudiante = @id_estudiante and c.Id_Curso = @id_curso
    group by c.Id_Curso , e.Id_Estudiante , Nota_Final
go
    exec Nota_Final 'A001','A02'
GO

2

Author: Joacer, 2016-09-14

Source

2 answers

score 1 · Answer 1

If you don't need any data from the student or the course, then there's no need to join those tables. Simply use only the table Evaluacion:

SELECT  id_Cursom
        id_EStudiante,
        Nota_Final = 0.2*PC1 + 0.2*PC2 + 0.3*TF + 0.3*EB
FROM (  SELECT  id_Curso,
                id_Estudiante,
                MIN(CASE WHEN Tipo = 'PC1' THEN Nota END) PC1,
                MIN(CASE WHEN Tipo = 'PC2' THEN Nota END) PC2,
                MIN(CASE WHEN Tipo = 'TF' THEN Nota END) TF,
                MIN(CASE WHEN Tipo = 'EB' THEN Nota END) EB
        FROM Evaluacion
        WHERE Id_Estudiante = @id_estudiante 
        AND Id_Curso = @id_curso
        GROUP BY id_Curso,
                 id_Estudiante) T
;

In case you need to calculate an average even when there is no grade, then you can use the minimum grade:

SELECT  id_Cursom
        id_EStudiante,
        Nota_Final = 0.2*ISNULL(PC1,5) + 0.2*ISNULL(PC2,5) + 0.3*ISNULL(TF,5) + 0.3*ISNULL(EB,5)
FROM (  SELECT  id_Curso,
                id_Estudiante,
                MIN(CASE WHEN Tipo = 'PC1' THEN Nota END) PC1,
                MIN(CASE WHEN Tipo = 'PC2' THEN Nota END) PC2,
                MIN(CASE WHEN Tipo = 'TF' THEN Nota END) TF,
                MIN(CASE WHEN Tipo = 'EB' THEN Nota END) EB
        FROM Evaluacion
        WHERE Id_Estudiante = @id_estudiante 
        AND Id_Curso = @id_curso
        GROUP BY id_Curso,
                 id_Estudiante) T
;

score 0 · Answer 2

Maybe you should select by course and student in the subqueries:

Nota_Final = (Select Nota * 0.20 from Evaluacion WHERE Tipo ='PC1' AND Id_Estudiante=@id_estudiante AND Id_Curso=@id_curso) 
  + (Select Nota * 0.20 from Evaluacion WHERE Tipo ='PC2' AND Id_Estudiante=@id_estudiante AND Id_Curso=@id_curso) 
  + (Select Nota * 0.30 from Evaluacion WHERE Tipo ='TF' AND Id_Estudiante=@id_estudiante AND Id_Curso=@id_curso)  
  + (Select Nota * 0.30 from Evaluacion WHERE Tipo ='EB' AND Id_Estudiante=@id_estudiante AND Id_Curso=@id_curso)