1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| BEGIN
FOR
select
d.code_unite,
d.nom_unite,
sum( c.frais_total ) frais_total,
sum( a.montant_facture ) montant_facture,
sum( ( e.volume_mj_sortie ) * ( e.nombre_jour_reel ) ) volume_mj_sortie,
/* calcul du ratio d'épuration */
(
( sum( c.frais_total ) + sum( a.montant_facture ) ) /
( sum( ( e.volume_mj_sortie ) * ( e.nombre_jour_reel ) ) )
) ratio
from tb_energie a
inner join tb_step b on ( a.code_step = b.code_step )
inner join tb_frais_exploitations c on ( b.code_step = c.code_step )
and ( c.date_mois between :date_debut and :date_fin )
inner join tb_unite d on ( b.code_unite = d.code_unite )
inner join tb_process e on ( b.code_step = e.code_step )
and ( e.date_bilan between :date_debut and :date_fin )
where
(
a.date_mois between dateadd(month, -1, :date_debut) and dateadd(month, -1, :date_fin)
)
group by d.code_unite, d.nom_unite
into
:CODE_UNITE,
:NOM_UNITE,
:FRAIS_TOTAL,
:MONTANT_FACTURE,
:VOLUME_MJ_SORTIE,
:RATIO_EXP_EAU_EPUREE
do
begin
suspend;
end
end |
Partager