Hola a todos, en un sp me hace falta saber si alguna de las 2 subconsultas
que utilizo para calcular la columna PromFinal no me devuelve ningún
registro, entonces no debo dividir por 2.
IDEtapa 1 y 2):
PromFinal = ((SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 1)
+
(SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 2))/2
CREATE PROCEDURE pa_select_notas_por_cur_mat_1y2etapa (@Curso as char(3),
@IdMateria as int) AS
SELECT A.Apellidos+', '+A.Nombres as Alumno,
CASE WHEN N1.IDEtapa = 1 THEN N1.Nota ELSE NULL END AS Nota1Et,
CASE WHEN N1.IDEtapa = 2 THEN N1.Nota ELSE NULL END AS Nota2Et,
CASE WHEN N1.IDEtapa = 1 THEN CONVERT(char(10),N1.FechaExamen,103) ELSE ''
END AS FechaExamen1Et,
CASE WHEN N1.IDEtapa = 2 THEN CONVERT(char(10),N1.FechaExamen,103) ELSE ''
END AS FechaExamen2Et,
E.Descripcion as Etapa, M.Nombre as Materia, N1.Curso,
Prom1Etapa = (SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDEtapa = N1.IDEtapa AND N2.IDMateria
= N1.IDMateria AND N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 1),
Prom2Etapa = (SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDEtapa = N1.IDEtapa AND N2.IDMateria
= N1.IDMateria AND N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 2),
PromFinal = ((SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 1)
+
(SELECT ROUND(AVG(CAST(N2.Nota as FLOAT)),2)
FROM NOTAS N2
WHERE N2.CURSO = N1.CURSO AND N2.IDMateria = N1.IDMateria AND
N2.IdAlumno = N1.IdAlumno AND N2.IDEtapa = 2))/2
FROM NOTAS N1
INNER JOIN Materias M ON N1.IDMateria = M.IDMateria
INNER JOIN Alumnos A ON N1.IDAlumno = A.IDAlumno
INNER JOIN Etapas E ON N1.IDEtapa = E.IDEtapa
WHERE (N1.IDEtapa = 1 or N1.IDEtapa = 2) AND A.Curso = @Curso AND
N1.IdMateria = @IdMateria
ORDER BY A.Apellidos, A.Nombres, N1.IDEtapa
saludos y gracias.
Leer las respuestas