SQL016.sql

/*
	PLT031 - Consulta para geração do Mapa de Notas
	Create By Bitts
	(02/06/2016)
	
*/

DECLARE @SCHEMA VARCHAR(20) = 'USE CorporeRM';

EXEC sp_sqlexec @SCHEMA;

DECLARE @CODTURMA1_S VARCHAR(10) = 'EM201';
DECLARE @CODSTATUS_M INT = 39;
DECLARE @STATUS_M VARCHAR(20) = 'Matriculado';
DECLARE @SPERLETIVO_M VARCHAR(10) = '2016';

DECLARE @CODCOLIGADA_N INT = 3;
DECLARE @CODFILIAL_N INT = 3;

DECLARE @CODETAPA_N INT = 1;

WITH MAPA_FALTAS AS (
	SELECT	
		SALUNO.RA,
		PPESSOA.NOME,
		SMATRICPL.CODSTATUS,
		SSTATUS.DESCRICAO,
		ETAPA.CODCOLIGADA,
		ETAPA.CODFILIAL,
		ETAPA.IDPERLET,
		ETAPA.SPERLETIVO,
		ETAPA.CODTURMA,
		ETAPA.NOMEDISCIPLINA,
		ETAPA.CODETAPA,
		ETAPA.DESCRICAO AS DESCRICAOETAPA,
		ETAPA.TIPOETAPA,
		ETAPA.NOTAFALTA		
	FROM
		PPESSOA (NOLOCK)
			LEFT JOIN SALUNO (NOLOCK) ON 
				SALUNO.CODPESSOA = PPESSOA.CODIGO
			LEFT JOIN SMATRICPL (NOLOCK) ON
				SMATRICPL.CODCOLIGADA = SALUNO.CODCOLIGADA AND
				SMATRICPL.RA = SALUNO.RA
			LEFT JOIN SSTATUS (NOLOCK) ON 
				SMATRICPL.CODCOLIGADA = SSTATUS.CODCOLIGADA AND 
				SMATRICPL.CODSTATUS = SSTATUS.CODSTATUS 
			LEFT JOIN (
				SELECT 
					STURMADISC.CODCOLIGADA, 
					STURMADISC.IDTURMADISC,
					STURMADISC.CODFILIAL,
					STURMADISC.CODTURMA,
					STURMADISC.IDPERLET,
					SPLETIVO.DESCRICAO AS SPERLETIVO,
					STURMADISC.CODDISC,
					STURMADISC.IDHABILITACAOFILIAL,
					SDISCIPLINA.NOME AS NOMEDISCIPLINA,
					SETAPAS.CODETAPA,
					SETAPAS.DESCRICAO,
					SNOTAETAPA.TIPOETAPA,
					SNOTAETAPA.NOTAFALTA,
					SNOTAETAPA.AULASDADAS,
					SNOTAETAPA.RA 
				FROM 
					STURMADISC (NOLOCK)
						LEFT JOIN SDISCIPLINA (NOLOCK) ON 
							SDISCIPLINA.CODDISC = STURMADISC.CODDISC AND
							SDISCIPLINA.CODCOLIGADA = STURMADISC.CODCOLIGADA AND 
							SDISCIPLINA.CODTIPOCURSO = STURMADISC.CODTIPOCURSO
						LEFT JOIN SETAPAS (NOLOCK) ON 
							SETAPAS.IDTURMADISC = STURMADISC.IDTURMADISC AND
							SETAPAS.CODCOLIGADA = STURMADISC.CODCOLIGADA
						LEFT JOIN SNOTAETAPA (NOLOCK) ON 
							SNOTAETAPA.IDTURMADISC = SETAPAS.IDTURMADISC AND 
							SNOTAETAPA.CODETAPA = SETAPAS.CODETAPA AND 
							SNOTAETAPA.TIPOETAPA = SETAPAS.TIPOETAPA	
						LEFT JOIN SPLETIVO (NOLOCK) ON
							SPLETIVO.CODCOLIGADA = STURMADISC.CODCOLIGADA AND
							SPLETIVO.IDPERLET = STURMADISC.IDPERLET
								
			) ETAPA (
				CODCOLIGADA, 
				IDTURMADISC,
				CODFILIAL,
				CODTURMA,
				IDPERLET,
				SPERLETIVO,
				CODDISC,
				IDHABILITACAOFILIAL,
				NOMEDISCIPLINA,
				CODETAPA,
				DESCRICAO,
				TIPOETAPA,
				NOTAFALTA,
				AULASDADAS,
				RA
			) ON 
		ETAPA.CODTURMA = SMATRICPL.CODTURMA
	AND ETAPA.RA = SMATRICPL.RA
	AND ETAPA.IDPERLET = SMATRICPL.IDPERLET 
	AND ETAPA.CODCOLIGADA = SMATRICPL.CODCOLIGADA
	AND ETAPA.CODFILIAL = SMATRICPL.CODFILIAL
	AND ETAPA.IDHABILITACAOFILIAL = SMATRICPL.IDHABILITACAOFILIAL
)
SELECT 
	N.*
FROM MAPA_FALTAS AS T
	PIVOT(
		MAX(T.NOTAFALTA) FOR T.NOMEDISCIPLINA IN (
			[LÍNGUA PORTUGUESA],
			[LITERATURA],
			[LÍNGUA INGLESA],
			[LÍNGUA ESPANHOLA],
			[ARTES],
			[EDUCAÇÃO FÍSICA],
			[MATEMÁTICA],
			[HISTÓRIA],
			[GEOGRAFIA],
			[FILOSOFIA],
			[SOCIOLOGIA],
			[ENSINO RELIGIOSO],
			[FÍSICA],
			[QUÍMICA],
			[BIOLOGIA]
		)
	) AS N 
WHERE 
	CODSTATUS = @CODSTATUS_M AND
	DESCRICAO = @STATUS_M AND
	CODTURMA = @CODTURMA1_S AND
	SPERLETIVO = @SPERLETIVO_M AND 
	CODCOLIGADA = @CODCOLIGADA_N AND
	CODFILIAL = @CODFILIAL_N AND
	CODETAPA = @CODETAPA_N;