7/23/2019 Script Ticket Media

1/2

select ds_periodo,no_curso,ds_turma,nu_anovenc,nu_mesvenc,qt_alunostotal,

qt_alunosfinanceiro,vl_orig,vl_grat,vl_liq,vl_orig/isnull(qt_alunosfinanceiro,1) as med_orig,vl_grat/isnull(qt_alunosfinanceiro,1) as med_grat,vl_liq/isnull(qt_alunosfinanceiro,1) as med_liq,no_unidade,ds_grupo

from (

select ds_periodo,no_curso,co_turma,ds_turma,nu_anovenc,nu_mesvenc,sum(vl_orig) as vl_orig,sum(vl_grat) as vl_grat,sum(vl_liq) as vl_liq,

(select count(*) from alunosmatr where seqturma=co_turma and alunosmatr.codstatus in (@CO_STATUSACAD)) as qt_alunostotal, (select count(*) from alunosmatr where seqturma=co_turma and alunosmatr.codstatus in (@CO_STATUSACAD) and alunosmatr.seqmatr in(select seqmatr from parcserv)) as qt_alunosfinanceiro,no_unidade,ds_grupo

from (select cursos.sigla as no_curso,

turmas.seqturma as co_turma,turmas.turma as ds_turma,periodos.nome as ds_periodo,

IsNull(parcdoc.valorig,0) as vl_orig,IsNull((select sum(valgrat) from parcgrat where parcgrat.seqdoc=parcdoc.seqdoc),0) as vl_grat,

valorig-IsNull((select sum(valgrat) from parcgrat whereparcgrat.seqdoc=parcdoc.seqdoc),0) as vl_liq,

year(parcdoc.datavenc) as nu_anovenc,month(parcdoc.datavenc) as nu_mesvenc,un.no_unidade,op.d

s_grupofrom parcdocinner join parcserv on parcdoc.seqserv=parcserv.seqservinner join alunosmatr on parcserv.seqmatr=alunosmatr.seqmatrinner join alunoscursos on alunosmatr.seqalucur=alunoscursos.seq

alucur

inner join cursos on alunoscursos.codcurso=cursos.codcursoinner join precos on parcserv.seqpreco=precos.seqprecoinner join turmas on alunosmatr.seqturma=turmas.seqturmainner join periodos on turmas.seqperiodo=periodos.seqperiodo

inner join institestab un on un.seqcad=turmas.seqestab inner join ge_tb_grupooperacional op on op.co_grupo=un.co_grupoop

7/23/2019 Script Ticket Media

2/2

where @DS_WHERETURMASand parcdoc.datavenc>=@DT_INICIALand parcdoc.datavenc