select msk.codpro, pro.fampro, pro.sfapro, pro.ssfpro, pro.nompro, msk.datmvt, msk.typosk, msk.codosk, msk.achvte, msk.typeve, msk.numeve, decode(msk.codosk, 'ENTREE', msk.qteope, 'TRFENT', msk.qteope, 'INICPT', msk.qteope, 'INIT', msk.qteope, 'VREAVN', -1*msk.qteope, 0) "Entrées", decode(msk.codosk, 'TRFSOR', msk.qteope, 'VLIVVN', msk.qteope, 0) "Réparés", decode(msk.codosk, 'SORTIE', msk.qteope, 0) "Détruits", msk.prxvte, msk.prxrev, tsc.prxtar, ret.typtie, ret.sigtie, ret.refext,
rettn.commen1, rettn.commen2, rettn.commen3, rettn.commen4, ent.sigdep, sum(faa.qtecde) afaa
from msk
inner join pro on pro.codsoc = msk.codsoc and pro.codpro = msk.codpro
inner join tsc on tsc.codsoc = pro.codsoc and tsc.codpro = pro.codpro and tsc.achvte = 'V'
inner join evp faa on faa.codsoc = pro.codsoc and faa.achvte = 'A' and faa.typeve = 'FAA' and faa.codpro = pro.codpro
left outer join msk ent on ent.codsoc = msk.codsoc and ent.codpro = msk.codpro and ent.datmvt = msk.datmvt and ent.heumvt = msk.heumvt and ent.codosk = 'TRFENT'
left outer join eve ret on ret.codsoc = msk.codsoc and ret.achvte = msk.achvte and ret.typeve = msk.typeve and ret.numeve = msk.numeve
left outer join evp retp on retp.codsoc = ret.codsoc and retp.achvte = ret.achvte and retp.typeve = ret.typeve and retp.numeve = ret.numeve and retp.numpos = msk.poseve
left outer join evt rettn on rettn.codsoc = retp.codsoc and rettn.achvte = retp.achvte and rettn.typeve = retp.typeve and rettn.numeve = retp.numeve and rettn.numpos = retp.numpos
where msk.codsoc = 2
and msk.sigdep = 'DPT3'
and msk.codosk in ('SORTIE', 'ENTREE', 'TRFSOR', 'TRFENT', 'INICPT', 'INIT', 'VREAVN', 'VLIVVN')
and msk.datmvt like '2006%'
and tsc.datdeb =
(
select max(datdeb)
from tsc tsc2
where tsc2.codsoc = tsc.codsoc
and tsc2.achvte = tsc.achvte
and tsc2.codpro = tsc.codpro
and tsc2.prxtar =
(
select max(prxtar)
from tsc tsc3
where tsc3.codsoc = tsc2.codsoc
and tsc3.achvte = tsc2.achvte
and tsc3.codpro = tsc2.codpro
)
)
group by msk.codpro, pro.fampro, pro.sfapro, pro.ssfpro, pro.nompro, msk.datmvt, msk.typosk, msk.codosk, msk.achvte, msk.typeve, msk.numeve, msk.qteope, msk.prxvte, msk.prxrev,
tsc.prxtar,
ret.typtie, ret.sigtie, ret.refext,
rettn.commen1, rettn.commen2, rettn.commen3, rettn.commen4, ent.sigdep
|