SQL019.sql

/*
	PLT38 - Script para relatório
	Create By Bitts 
	(10/10/2016)
	
	Para utilização em Cabeçalho de Relatório
  Turma / professor / titular / aluno
*/

SELECT
	
	K.IDPROFESSORTURMA,
	A.CODTURMA,
	A.IDPERLET,
	A.RA,
	B.CODSTATUS,
	B.DESCRICAO AS STATUS,

	D.SEXO,
	CONVERT (VARCHAR,D.DTNASCIMENTO, 103) AS DTNASCIMENTO,
	D.ALUNO,
	
	F.NOME AS NOME_CURSO,
	
	J.CODDISC,
	J.NOME AS NOME_DISCIPLINA,
	CASE 
		WHEN N.TIPO ='M' THEN 'Manhã'
		WHEN N.TIPO ='V' THEN 'Tarde'
		WHEN N.TIPO ='I' THEN 'Integral'
		ELSE N.NOME
	END AS TURNO,
	UPPER(M.NOME) AS NOME_PROFESSOR,
	
	ISNULL (CONVERT(VARCHAR, A.NUMALUNO, 1), '99') AS NUM_ALUNO,
	P.NOME AS NOME_ALUNO,
	P.SEXO,
	P.ALUNO,
	FLOOR(DATEDIFF(DAY, P.DTNASCIMENTO, GETDATE()) / 365.25) AS IDADE,
	B.CODSTATUS
FROM 
	SMATRICPL AS A (NOLOCK)
	
		LEFT JOIN SSTATUS AS B  (NOLOCK)ON 
			A.CODCOLIGADA = B.CODCOLIGADA AND
			A.CODSTATUS = B.CODSTATUS
			
		LEFT JOIN SALUNO AS C (NOLOCK) ON 
			C.CODCOLIGADA = A.CODCOLIGADA AND
			C.RA = A.RA
			
		LEFT JOIN PPESSOA AS D (NOLOCK) ON 
			D.CODIGO = C.CODPESSOA
			
		LEFT JOIN SHABILITACAOFILIAL AS E (NOLOCK) ON 
			E.CODCOLIGADA = A.CODCOLIGADA AND
			E.IDHABILITACAOFILIAL = A.IDHABILITACAOFILIAL AND 
			E.CODFILIAL = A.CODFILIAL
			
		LEFT JOIN SCURSO AS F (NOLOCK)ON
			F.CODCOLIGADA = E.CODCOLIGADA AND
			F.CODCURSO = E.CODCURSO
		
		LEFT JOIN SPLETIVO AS G (NOLOCK) ON
			G.CODCOLIGADA = A.CODCOLIGADA AND
			G.IDPERLET = A.IDPERLET
			
		LEFT JOIN STURMA AS H (NOLOCK) ON 
			H.CODCOLIGADA = A.CODCOLIGADA AND
			H.CODFILIAL = A.CODFILIAL AND
			H.CODTURMA = A.CODTURMA AND
			H.IDPERLET = A.IDPERLET AND 
			H.IDHABILITACAOFILIAL = A.IDHABILITACAOFILIAL
			
		LEFT JOIN STURMADISC AS I (NOLOCK) ON
			I.CODCOLIGADA = H.CODCOLIGADA AND
			I.CODFILIAL = H.CODFILIAL AND
			I.CODTURMA = H.CODTURMA AND
			I.IDPERLET = H.IDPERLET AND 
			I.IDHABILITACAOFILIAL = H.IDHABILITACAOFILIAL	
		
		LEFT JOIN SDISCIPLINA AS J (NOLOCK) ON
			J.CODCOLIGADA = I.CODCOLIGADA AND
			J.CODDISC = I.CODDISC 
			
		LEFT JOIN SPROFESSORTURMA AS K (NOLOCK)	ON 
			K.CODCOLIGADA = I.CODCOLIGADA AND
			K.IDTURMADISC = I.IDTURMADISC
			
		LEFT JOIN SPROFESSOR AS L (NOLOCK) ON
			L.CODPROF = K.CODPROF
			
		LEFT JOIN PPESSOA AS M (NOLOCK) ON
			L.CODPESSOA = M.CODIGO
			
		LEFT JOIN STURNO AS N (NOLOCK) ON
			N.CODCOLIGADA = I.CODCOLIGADA AND
			N.CODTURNO = I.CODTURNO
		
		LEFT JOIN SALUNO AS O (NOLOCK) ON 
			O.CODCOLIGADA = A.CODCOLIGADA AND
			O.RA = A.RA
			
		LEFT JOIN PPESSOA AS P (NOLOCK) ON 
			P.CODIGO = O.CODPESSOA
WHERE 

	B.DESCRICAO IN ('Matriculado','Transferido','Pré-Matriculado')
	AND A.CODTURMA = 'ef61'
	--AND A.CODFILIAL = '3'
	--AND K.TIPOPROF = 'T'
	--AND A.RA = '0003775'

ORDER BY 
	A.NUMALUNO