text: /*
  	PLT062 - Caderno de Chamada preenchido - sem relação professor
  	Create By Bitts
  	(06/07/2017)
  */
  
  
  DECLARE @IDTURMADISC VARCHAR(5) = '4324';
  DECLARE @CODCOLIGADA VARCHAR(3) = '3';
  
  DECLARE @CODPERLET VARCHAR(10) = '2017';
  DECLARE @TIPOETAPA VARCHAR(3) = 'F';
  DECLARE @CODETAPA VARCHAR(5) = '6';
  DECLARE @DATA_INICIO VARCHAR(15) = '15/02/2017';
  DECLARE @DATA_FIM VARCHAR(15) = '20/05/2017';
  DECLARE @CODTURMA VARCHAR(10) = 'EF21';
  
  DECLARE @NCOLUMN INT = 18;
  
  
  SELECT 
  	A.CODCOLIGADA,
  
  	A.CODFILIAL,
  	G.NOME AS NOMEFILIAL,
  	H.IMAGEM,
  
  	E.NOME AS NOMEHABILITACAO,
  	F.NOME AS NOMECURSO,
  	D.NOME AS NOMETURNO,
  	B.CODPERLET,
  	B.IDPERLET,
  	
  	A.IDTURMADISC,
  	A.CODTURMA,
  	A.CODDISC,
  	L.NOME AS [NOME DISCIPLINA],
  	CASE WHEN (R.APRESENTACAO = 0) THEN T.CH ELSE T.CH END AS CH,
  
  	TITULAR.NOME AS TITULAR, 
  	SUBSTITUTO.NOME AS SUBSTITUTO,
  
  	O.NUMDIARIO,
  	O.RA,	
  	PPESSOAALUNO.NOME AS NOMEALUNO,
  	R.APRESENTACAO,
  	CASE S.PRESENCA WHEN 'A' THEN 'F' ELSE '.' END AS PRESENCA,
  	K.DATA,
  	K.AULA,
  	S.JUSTIFICADA,
  	I.DESCRICAO AS NOMEETAPA,
  	I.CODETAPA,
  	I.AULASDADAS,
  	I.AULASPREVISTAS,
  	SSTATUSRES.DESCRICAO AS NOMESTATUSRES,
  	CEILING(ROW_NUMBER() OVER(PARTITION BY PPESSOAALUNO.NOME ORDER BY K.DATA) / @NCOLUMN) AS QUEBRA,
  	CONVERT(INT, ((ROW_NUMBER() OVER (PARTITION BY PPESSOAALUNO.NOME ORDER BY K.DATA)) / @NCOLUMN) +1) AS QUEBRA1,
  	SSTATUSRES.DESCRICAO AS STATUS,
  	V.DATATRANSFERENCIA  
  FROM
  	STURMADISC AS A (NOLOCK)
  	LEFT JOIN SPLETIVO AS B (NOLOCK) ON
  		B.CODCOLIGADA = A.CODCOLIGADA
  		AND B.CODFILIAL = A.CODFILIAL
  		AND B.IDPERLET = B.IDPERLET
  	LEFT JOIN SHABILITACAOFILIAL AS C (NOLOCK) ON
  		C.CODCOLIGADA = A.CODCOLIGADA
  		AND C.IDHABILITACAOFILIAL = A.IDHABILITACAOFILIAL
  	LEFT JOIN STURNO AS D (NOLOCK) ON
  		D.CODCOLIGADA = A.CODCOLIGADA
  		AND D.CODFILIAL = A.CODFILIAL
  		AND D.CODTURNO = C.CODTURNO
  	LEFT JOIN SHABILITACAO AS E (NOLOCK) ON 
  		E.CODCOLIGADA = C.CODCOLIGADA AND
  		E.CODCURSO = C.CODCURSO AND
  		E.CODHABILITACAO = C.CODHABILITACAO
  	LEFT JOIN SCURSO AS F (NOLOCK) ON 
  		F.CODCOLIGADA = C.CODCOLIGADA AND
  		F.CODCURSO = C.CODCURSO
  	LEFT JOIN GFILIAL AS G (NOLOCK) ON 
  		G.CODCOLIGADA = A.CODCOLIGADA AND
  		G.CODFILIAL = A.CODFILIAL
  	LEFT JOIN GIMAGEM AS H (NOLOCK) ON 
  		H.ID = G.IDIMAGEM
  	LEFT JOIN (
  		SELECT
  			A.CODCOLIGADA, A.CODPROF, A.IDPROFESSORTURMA, A.IDTURMADISC, B.CODPESSOA, C.NOME
  		FROM
  			SPROFESSORTURMA AS A (NOLOCK)
  			RIGHT JOIN SPROFESSOR AS B ON
  				B.CODPROF = A.CODPROF
  				AND A.TIPOPROF = 'T'
  			RIGHT JOIN PPESSOA AS C ON
  				C.CODIGO = B.CODPESSOA
  	) AS TITULAR ON
  		TITULAR.IDTURMADISC = A.IDTURMADISC
  	JOIN (
  		SELECT D.IDTURMADISC, REPLACE( REPLACE(
  			(
  				SELECT
  					C.NOME + ' (' + CONVERT(VARCHAR, A.DTINICIO, 103) + ' - '+ CONVERT(VARCHAR, A.DTFIM, 103) +')' AS NOME
  				FROM
  					SPROFESSORTURMA AS A (NOLOCK)
  					RIGHT JOIN SPROFESSOR AS B ON
  						B.CODPROF = A.CODPROF
  						AND A.TIPOPROF <> 'T'
  					RIGHT JOIN PPESSOA AS C ON
  						C.CODIGO = B.CODPESSOA
  					WHERE A.IDTURMADISC = D.IDTURMADISC
  					FOR XML PATH('') 
  			), '<NOME>', ''),
  			'</NOME>', '; ')	
  		FROM 
  			SPROFESSORTURMA AS D
  		GROUP BY D.IDTURMADISC
  	)  AS SUBSTITUTO (IDTURMADISC, NOME) ON
  		SUBSTITUTO.IDTURMADISC = A.IDTURMADISC
  	LEFT JOIN SETAPAS AS I (NOLOCK) ON 
  		I.CODCOLIGADA = A.CODCOLIGADA AND 
  		I.IDTURMADISC = A.IDTURMADISC
  	LEFT JOIN SHORARIOTURMA AS J (NOLOCK) ON 
  		J.CODCOLIGADA = A.CODCOLIGADA AND 
  		J.IDTURMADISC = A.IDTURMADISC AND
  		J.CODFILIAL = A.CODFILIAL
  	LEFT JOIN SPLANOAULA AS K (NOLOCK) ON 
  		K.CODCOLIGADA = A.CODCOLIGADA AND
  		K.IDTURMADISC = A.IDTURMADISC AND 
  		K.CODHOR = J.CODHOR
  	LEFT JOIN SDISCIPLINA  AS L (NOLOCK) ON 
  		L.CODCOLIGADA = A.CODCOLIGADA AND
  		L.CODDISC = A.CODDISC
  	LEFT JOIN SPLETIVO AS M (NOLOCK) ON 
  		M.CODCOLIGADA = A.CODCOLIGADA AND
  		M.IDPERLET = A.IDPERLET
  	LEFT JOIN SHABILITACAOFILIAL AS N (NOLOCK) ON 
  		N.CODCOLIGADA = A.CODCOLIGADA AND
  		N.IDHABILITACAOFILIAL = A.IDHABILITACAOFILIAL
  	LEFT JOIN SMATRICULA AS O (NOLOCK) ON 
  		O.CODCOLIGADA = A.CODCOLIGADA AND
  		O.IDTURMADISC = A.IDTURMADISC
  	LEFT JOIN SALUNO AS P (NOLOCK) ON 
  		P.CODCOLIGADA  = O.CODCOLIGADA AND
  		P.RA = O.RA
  	LEFT JOIN PPESSOA AS PPESSOAALUNO (NOLOCK) ON 
  		PPESSOAALUNO.CODIGO = P.CODPESSOA
  	LEFT JOIN SSTATUS AS Q (NOLOCK) ON 
  		Q.CODCOLIGADA = O.CODCOLIGADA AND
  		Q.CODSTATUS = O.CODSTATUS
  	LEFT JOIN STIPOCURSO AS R (NOLOCK) ON 
  		R.CODCOLIGADA = A.CODCOLIGADA AND
  		R.CODTIPOCURSO = A.CODTIPOCURSO
  	LEFT JOIN SFREQUENCIA AS S (NOLOCK) ON 
  		S.CODCOLIGADA = O.CODCOLIGADA AND
  		S.IDTURMADISC = O.IDTURMADISC AND
  		S.RA = O.RA AND
  		S.DATA = K.DATA
  	LEFT JOIN SSTATUS AS SSTATUSRES (NOLOCK) ON 
  		SSTATUSRES.CODCOLIGADA = O.CODCOLIGADA AND
  		SSTATUSRES.CODSTATUS = O.CODSTATUSRES
  	LEFT JOIN SDISCGRADE AS T (NOLOCK) ON 
  		T.CODCOLIGADA = N.CODCOLIGADA AND
  		T.CODCURSO = N.CODCURSO AND
  		T.CODHABILITACAO = N.CODHABILITACAO AND
  		T.CODGRADE = N.CODGRADE AND
  		T.CODDISC = A.CODDISC
  	LEFT JOIN SMATRICPL AS U (NOLOCK) ON 
  		U.IDPERLET = O.IDPERLET AND
     	 	U.IDHABILITACAOFILIAL = O.IDHABILITACAOFILIAL AND
     	 	U.RA = O.RA
  	LEFT JOIN SMATRICPLCOMPL AS V (NOLOCK) ON 
  		V.IDPERLET = U.IDPERLET AND
     		V.IDHABILITACAOFILIAL = U.IDHABILITACAOFILIAL AND
     		V.RA = U.RA
  WHERE 
  	A.CODTIPOCURSO = '5' 
  	AND A.ATIVA = 'S' 
  	AND Q.PLDIARIO = 'S'
  	AND (CONVERT(DATETIME, CONVERT(CHAR, K.DATA, 103),103) >= CONVERT(DATETIME, CONVERT(CHAR, I.DTINICIO, 103),103) OR I.DTINICIO IS NULL)
  	AND (CONVERT(DATETIME, CONVERT(CHAR, K.DATA, 103),103) <= CONVERT(DATETIME, CONVERT(CHAR, I.DTFIM, 103),103) OR I.DTFIM IS NULL)
  	AND A.IDTURMADISC = @IDTURMADISC 
  	AND A.CODCOLIGADA = @CODCOLIGADA
  	AND A.CODTURMA LIKE '%'+ @CODTURMA +'%'
  	AND B.CODPERLET LIKE '%'+ @CODPERLET +'%'
  	AND I.TIPOETAPA = @TIPOETAPA
  	AND I.CODETAPA = @CODETAPA
  	AND K.DATA BETWEEN CONVERT(DATETIME, @DATA_INICIO) AND CONVERT(DATETIME, @DATA_FIM)
  
  
  
  