Henry Paul Garcia Huacachi <henry.garcia@zarate.edu.pe>

Henry te envío un nuevo código para extraer notas,...1 mensaje

William Alejandro <william.alejandro@zarate.edu.pe> 10 de diciembre de 2013, 9:41Para: Henry Paul Garcia Huacachi <henry.garcia@zarate.edu.pe>

Henry, inclusive el código lo puedes volver dinámico para que solo muestre las columnas necesarias ..

DECLARE @IdCurso int=16489

SELECT cCodAlu=MAX(A.cCodAlu),

A.IdMatricula, D.IdCurso,

cAlumno = MAX(B.cPaterno +' '+ B.cMaterno +', '+ B.cNombre),

S1N1 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=1 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S1N2 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=2 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S1N3 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=3 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S1N4 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=4 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S1N5 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=5 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S1N6 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=1AND E.nNroNota=6 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N1 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=1 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N2 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=2 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N3 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=3 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N4 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=4 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N5 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=5 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S2N6 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=2AND E.nNroNota=6 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

EQ = RIGHT('00' +CAST(CAST(ROUND(MAX(ISNULL(A.nExaQui,0)),0) AS int) as varchar(2)),2),

S3N1 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=1 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

Correo de Corporación Educativa ZAVER SAC - Henry te envío un n... https://mail.google.com/mail/u/0/?ui=2&ik=913b0f421c&view=pt&se...

1 de 2 07/01/2014 17:41

S3N2 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=2 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S3N3 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=3 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S3N4 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=4 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S3N5 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=5 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S3N6 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=3AND E.nNroNota=6 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N1 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=1 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N2 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=2 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N3 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=3 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N4 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=4 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N5 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=5 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

S4N6 = RIGHT('00' +CAST(CAST(ROUND(SUM(ISNULL(CASE WHEN E.nNroSem=4AND E.nNroNota=6 THENE.nNota ELSE 0 END,0)),0) AS int) as varchar(2)),2),

EM = RIGHT('00' +CAST(CAST(ROUND(MAX(ISNULL(A.nExaMen,0)),0) AS int) as varchar(2)),2)

FROM MMatricula A INNER JOIN MAlumno B

ON A.cCodAlu=B.cCodAlu AND A.cEstMatricula='A' AND B.cEstAlu='A'

INNER JOIN DCursoXGrado D

ON D.cAnio+D.cMes+D.cCodModalidad+D.cCodHorario+D.cCodGrado+D.cCodSeccion=

A.cAnio+A.cMes+A.cCodModalidad+A.cCodHorario+A.cCodGrado+A.cCodSeccion

AND D.cEstado='A' AND D.IdCurso = @IdCurso

LEFT JOIN MNotasDet E

ON E.IdMatricula=A.IdMatricula AND E.IdCurso=@IdCurso AND E.cEstado='A'

GROUP BY A.IdMatricula, D.IdCurso

ORDER BY 4

Correo de Corporación Educativa ZAVER SAC - Henry te envío un n... https://mail.google.com/mail/u/0/?ui=2&ik=913b0f421c&view=pt&se...

2 de 2 07/01/2014 17:41