SQL032.sql

/*
	PLT059 - Script para Boletim por Turma
	Create By Bitts
	(23/06/2017)
	
	Para utilização em RELATÓRIOS
	
	Upgrade:
	- 22/06/2017 
		- Utilização de Variaveis
		- definição de novo parametro para exibir notas de acordo com o Trimestre passado
		
*/


DECLARE @CODCOLIGADA INT = '3';
DECLARE @CODFILIAL INT = '3';
DECLARE @CODPERLET VARCHAR(5) = '2017';
DECLARE @TRIMESTRE_ INT = 3;
DECLARE @CODTURMA_ VARCHAR(5) = 'EF71';

SELECT
	A.CODFILIAL,
	F.NOME AS ALUNO,
	A.RA AS RA,
	I.NOME AS CURSO,
	A.CODTURMA AS TURMA,
	D.NUMDIARIO AS NUMCHAMADA,
	L.NOME AS DISCIPLINA,
	Q.CH AS CARGA_HORARIA,
	C.CODPERLET AS PLETIVO,
	P.NOME AS SERIE,
	A.CODSTATUSRES,
	C.DIASLETIVOS,
	N1 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 1 THEN ISNULL(CONVERT(VARCHAR, N1.NOTA),'-') ELSE '' END)),
	F1 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 1 THEN ISNULL(CONVERT(VARCHAR, F1.FALTAS),'0') ELSE '' END)), 

	N2 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 2 THEN ISNULL(CONVERT(VARCHAR, N2.NOTA),'-') ELSE '' END)),
	F2 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 2 THEN ISNULL(CONVERT(VARCHAR, F2.FALTAS),'0') ELSE '' END)), 
	
	N3 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 3 THEN ISNULL(CONVERT(VARCHAR, N3.NOTA),'-') ELSE '' END)),
	F3 = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 3 THEN ISNULL(CONVERT(VARCHAR, F3.FALTAS),'0') ELSE '' END)), 
	
	MA = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 3 THEN ISNULL(CONVERT(VARCHAR, MA.NOTA),'-') ELSE '' END)),
	EX = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 3 THEN ISNULL(CONVERT(VARCHAR, EX.NOTA),'-') ELSE '' END)),
	NF = CONVERT(VARCHAR, (CASE WHEN @TRIMESTRE_ >= 3 THEN ISNULL(CONVERT(VARCHAR, (ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) +ISNULL(F3.FALTAS,0))),'-') ELSE '' END)),
	MEDIAANUAL = (
		CASE 
			WHEN N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) >= 7    
			THEN (N1.NOTA + N2.NOTA + N3.NOTA)/3  
	
			WHEN N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) < 7 AND EX.NOTA IS NOT NULL
			THEN ((N1.NOTA + N2.NOTA + N3.NOTA)/3) 
	
		ELSE NULL END),
	AULAS.TOTAL AS TOTALAULAS,
	SITUACAO = (CASE WHEN N.DESCRICAO = 'Matriculado' THEN '-' ELSE N.DESCRICAO END),	
	RESFINAL = (CASE WHEN O.DESCRICAO IS NULL THEN ''ELSE O.DESCRICAO END)
FROM
	
	SMATRICPL AS A (NOLOCK)
		INNER JOIN GFILIAL AS B (NOLOCK) ON
			A.CODCOLIGADA = B.CODCOLIGADA AND
			A.CODFILIAL = B.CODFILIAL
		INNER JOIN SPLETIVO AS C (NOLOCK) ON
			A.CODCOLIGADA = C.CODCOLIGADA AND
			A.IDPERLET = C.IDPERLET
		INNER JOIN SMATRICULA AS D (NOLOCK) ON
			A.CODCOLIGADA = D.CODCOLIGADA AND
			A.IDPERLET = D.IDPERLET AND
			A.RA = D.RA AND
			A.IDHABILITACAOFILIAL = D.IDHABILITACAOFILIAL
		INNER JOIN SALUNO AS E (NOLOCK) ON
			D.CODCOLIGADA = E.CODCOLIGADA AND 
			E.CODTIPOCURSO = C.CODTIPOCURSO AND
			D.RA = E.RA 
		INNER JOIN PPESSOA AS F (NOLOCK) ON
			E.CODPESSOA = F.CODIGO
		INNER JOIN SHABILITACAOFILIAL AS G (NOLOCK) ON
			A.CODCOLIGADA = G.CODCOLIGADA AND
			A.IDHABILITACAOFILIAL = G.IDHABILITACAOFILIAL AND 
			A.CODFILIAL = G.CODFILIAL
		INNER JOIN STURNO AS H (NOLOCK) ON
			H.CODCOLIGADA = G.CODCOLIGADA AND
			H.CODTURNO = G.CODTURNO AND 
			H.CODFILIAL = G.CODFILIAL AND 
			H.CODTIPOCURSO = C.CODTIPOCURSO
		LEFT JOIN SCURSO I (NOLOCK) ON
			I.CODCOLIGADA = G.CODCOLIGADA AND
			I.CODCURSO = G.CODCURSO 
		INNER JOIN SHABILITACAO AS J (NOLOCK) ON
			J.CODCOLIGADA = G.CODCOLIGADA AND
			J.CODCURSO = G.CODCURSO AND
			J.CODHABILITACAO = G.CODHABILITACAO
		INNER JOIN STURMADISC AS K (NOLOCK) ON
			K.CODCOLIGADA = D.CODCOLIGADA AND
			K.IDTURMADISC = D.IDTURMADISC 
		INNER JOIN SDISCIPLINA AS L (NOLOCK) ON 
			L.CODCOLIGADA = K.CODCOLIGADA AND
			L.CODDISC = K.CODDISC 
		INNER JOIN SSTATUS AS N (NOLOCK) ON
			N.CODCOLIGADA = D.CODCOLIGADA AND
			N.CODSTATUS = D.CODSTATUS AND 
			N.CODTIPOCURSO = C.CODTIPOCURSO
		LEFT JOIN SSTATUS AS O (NOLOCK) ON
			O.CODCOLIGADA = A.CODCOLIGADA AND
			O.CODSTATUS = A.CODSTATUSRES AND 
			O.CODTIPOCURSO = C.CODTIPOCURSO
		INNER JOIN SHABILITACAO AS P (NOLOCK) ON
			P.CODCURSO = G.CODCURSO AND
			P.CODHABILITACAO = G.CODHABILITACAO	
		LEFT JOIN SDISCGRADE AS Q (NOLOCK) ON
			Q.CODDISC = L.CODDISC AND
			Q.CODHABILITACAO = P.CODHABILITACAO AND
			Q.CODCURSO = I.CODCURSO AND 
			Q.CODCOLIGADA = A.CODCOLIGADA AND 
			Q.CODGRADE = G.CODGRADE
		LEFT JOIN SPERIODO AS R (NOLOCK) ON
			R.CODCURSO = Q.CODCURSO AND
			R.CODHABILITACAO = P.CODHABILITACAO AND
			R.CODGRADE = Q.CODGRADE AND
			R.CODPERIODO = Q.CODPERIODO
		INNER JOIN STURMA AS S (NOLOCK) ON
			S.CODCOLIGADA = A.CODCOLIGADA AND
			S.CODFILIAL = A.CODFILIAL AND
			S.IDPERLET = A.IDPERLET AND
			S.CODTURMA = A.CODTURMA 
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) N1 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			N1.CODCOLIGADA = D.CODCOLIGADA AND 
			N1.IDTURMADISC = D.IDTURMADISC AND
			N1.RA = D.RA AND 
			N1.CODETAPA = '5'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) F1 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			F1.CODCOLIGADA = D.CODCOLIGADA AND 
			F1.IDTURMADISC = D.IDTURMADISC AND
			F1.RA = D.RA AND 
			F1.CODETAPA = '6'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) N2 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			N2.CODCOLIGADA = D.CODCOLIGADA AND 
			N2.IDTURMADISC = D.IDTURMADISC AND
			N2.RA = D.RA AND 
			N2.CODETAPA = '11'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) F2 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			F2.CODCOLIGADA = D.CODCOLIGADA AND 
			F2.IDTURMADISC = D.IDTURMADISC AND
			F2.RA = D.RA AND 
			F2.CODETAPA = '12'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) N3 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			N3.CODCOLIGADA = D.CODCOLIGADA AND 
			N3.IDTURMADISC = D.IDTURMADISC AND
			N3.RA = D.RA AND 
			N3.CODETAPA = '17'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) F3 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			F3.CODCOLIGADA = D.CODCOLIGADA AND 
			F3.IDTURMADISC = D.IDTURMADISC AND
			F3.RA = D.RA AND 
			F3.CODETAPA = '18'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) MA (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			MA.CODCOLIGADA = D.CODCOLIGADA AND 
			MA.IDTURMADISC = D.IDTURMADISC AND
			MA.RA = D.RA AND 
			MA.CODETAPA = '19'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) EX (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			EX.CODCOLIGADA = D.CODCOLIGADA AND 
			EX.IDTURMADISC = D.IDTURMADISC AND
			EX.RA = D.RA AND 
			EX.CODETAPA = '20'
		LEFT JOIN (
			SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA
		) NF (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
			NF.CODCOLIGADA = D.CODCOLIGADA AND 
			NF.IDTURMADISC = D.IDTURMADISC AND
			NF.RA = D.RA AND 
			NF.CODETAPA = '21'
		LEFT JOIN (
			SELECT RA, CONVERT(INT, AULASDADAS) AS TOTAL, CODCOLIGADA, IDTURMADISC, CODETAPA 
			FROM SNOTAETAPA X 
		) AULAS (RA, TOTAL, CODCOLIGADA, IDTURMADISC, CODETAPA) ON
				AULAS.CODCOLIGADA = D.CODCOLIGADA AND 
				AULAS.IDTURMADISC = D.IDTURMADISC AND
				AULAS.RA = D.RA AND 
				AULAS.CODETAPA = '9'
WHERE
	--S.CODCOLIGADA = 1 AND
	C.CODPERLET = @CODPERLET AND
	C.CODFILIAL = 3 AND
  
    A.CODSTATUS IN ('17','43','54','64','67','47','23','53','39','50','60','2','44','65','55','19','18','20','34','28','51','40','61') AND
    
	D.NUMDIARIO IS NOT NULL AND
	S.CODTURMA = 'EF71'
ORDER BY
	Q.POSHIST,
	I.CODCURSO,
	A.CODTURMA,
	A.RA,
	L.CODDISC