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
|
insert into essai (dateAffaire, nomRepresentant, totalAffaires, nbAffaires, totalDevis, nbDevis, totalCommandes, nbCommandes, nbVisites, nbRelances)
values select qvs.date_status_entity,
acm.id_contact,
sum(qvs.total_price_list + ovs.quoted_amount) as sommeAffaire,
0,
sum(qvs.total_price_list) as sommeDevis,
count(qvs.total_price_list) as nbDevis,
sum(ovs.quoted_amount) as sommeCommande,
count(ovs.quoted_amount) as nbCommande,
0,
0
from ... ovs,
... acm,
(select id_entity, short_text1, short_text2
from ...
where id_class = 'oppUps' and num_array = 0 and num_line = 0) ocl,
(select id_entity, short_text1
from ...
where id_class = 'oppCom' and num_array = 0 and num_line = 0) oclc,
... qvs,
(select id_entity, short_text1, medium_text1
from ...
where id_class = 'quoUps' and num_array = 0 and num_line = 0) qcl,
(select id_entity, short_text10
from ...
where id_class = 'accInfCom' and num_array = 0 and num_line = 0) accl,
... ctvs
where (qvs.flag_is_to_forecast=1 or qvs.flag_is_to_forecast=0)
and qvs.status_entity='WON'
and qvs.status_entity='OPEN'
and qvs.id_opportunity = ovs.id_opportunity
and qcl.id_entity = qvs.id_quote
and ctvs.date_status_entity = qvs.date_status_entity
and ovs.id_opportunity = ocl.id_opportunity
and qcl.medium_text1 = accl.id_account
GROUP BY sommeAffaire,
sommeDevis,
nbDevis,
sommeCommande,
nbCommande; |
Partager