SELECT CONVERT(VARCHAR(10),DATA,120) data,dbo.tiraAcentos(C.nome) as curso,ST.CODTURMA codtur,ISNULL(BT.CODSUBTURMA,'-') subturma,
                ST.CODDISC codmat,CASE WHEN HORAINICIAL IN ('13:00','13:50') THEN 1 WHEN HORAINICIAL IN ('15:00','15:50')
                THEN 2 WHEN HORAINICIAL IN ('16:00','16:50') THEN 3 WHEN HORAINICIAL IN ('17:00','17:50')
                THEN 4 WHEN HORAINICIAL IN ('18:00','18:50') THEN 5 WHEN HORAINICIAL IN ('19:00','19:50')
                THEN 6 WHEN HORAINICIAL IN ('21:00','21:50') THEN 7 ELSE 8 END CODIGO,
                dbo.tiraAcentos(D.NOME) materia,dbo.tiraAcentos(PP.NOME) professor,
                pp.codusuario codprofessor,MIN(HORAINICIAL) horainicio, MAX(HORAFINAL) horafim ,
                COUNT(DISTINCT SP.CODHOR) qtdaula,
                COUNT(DISTINCT M.RA) qtdaluno,
                WF.CODAREA codarea, WF.CODPOSITION codposition
                FROM SPLANOAULA SP
                INNER JOIN STURMADISC ST ON ST.CODCOLIGADA = SP.CODCOLIGADA AND ST.IDTURMADISC = SP.IDTURMADISC
                INNER JOIN SHABILITACAOFILIAL SF ON SF.CODCOLIGADA = ST.CODCOLIGADA AND SF.IDHABILITACAOFILIAL = ST.IDHABILITACAOFILIAL
                INNER JOIN SCURSO C ON C.CODCOLIGADA = SF.CODCOLIGADA AND C.CODCURSO = SF.CODCURSO
                INNER JOIN SDISCIPLINA D ON D.CODCOLIGADA = ST.CODCOLIGADA AND D.CODDISC = ST.CODDISC
                INNER JOIN SHORARIO H ON H.CODCOLIGADA = SP.CODCOLIGADA AND H.CODHOR = SP.CODHOR
                INNER JOIN SPROFESSORTURMA SU ON SU.CODCOLIGADA = ST.CODCOLIGADA AND SU.IDTURMADISC = ST.IDTURMADISC
                LEFT JOIN SPROFESSOR P ON P.CODCOLIGADA = SU.CODCOLIGADA AND P.CODPROF = SU.CODPROF
                LEFT JOIN PPESSOA PP ON PP.CODIGO = P.CODPESSOA
                LEFT JOIN SSUBTURMA BT ON BT.CODCOLIGADA = ST.CODCOLIGADA AND BT.IDTURMADISC = ST.IDTURMADISC
                LEFT JOIN SMATRICULA M ON M.CODCOLIGADA = ST.CODCOLIGADA AND M.IDTURMADISC = ST.IDTURMADISC AND ISNULL(M.CODSUBTURMA,'-') = ISNULL(BT.CODSUBTURMA,'-')
                LEFT JOIN SPROFESSORCOMPL el on P.codprof = EL.codprof AND P.CODCOLIGADA = EL.CODCOLIGADA
                LEFT JOIN orquestra.dbo.wfposition_area wf on wf.codposition = el.posicao_orquestra
                WHERE DATA = '".$data."' and M.CODSTATUS in (02,05,12,16,17,24,25,29,30)
                AND ISNULL(TIPOPROF,'T') = 'T' AND ISNULL(SU.DTFIM,GETDATE())>= DATEADD(DAY,-1,GETDATE())
                GROUP BY DATA,dbo.tiraAcentos(C.nome) ,ST.CODTURMA ,ISNULL(BT.CODSUBTURMA,'-'),
                ST.CODDISC ,CASE WHEN HORAINICIAL IN ('13:00','13:50') THEN 1 WHEN HORAINICIAL IN ('15:00','15:50')
                THEN 2 WHEN HORAINICIAL IN ('16:00','16:50') THEN 3 WHEN HORAINICIAL IN ('17:00','17:50')
                THEN 4 WHEN HORAINICIAL IN ('18:00','18:50') THEN 5 WHEN HORAINICIAL IN ('19:00','19:50')
                THEN 6 WHEN HORAINICIAL IN ('21:00','21:50') THEN 7 ELSE 8 END ,
                dbo.tiraAcentos(D.NOME) ,dbo.tiraAcentos(PP.NOME),pp.codusuario ,WF.CODAREA,WF.CODPOSITION";