1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
|
select
a1.commercial_cleunik AS commercial_cleunik,
round(sum(a1.htbrutremise)*100/a2.total_doc,2) as pourcentage,
round(a2.total_doc,2) as total_doc,round(sum(a1.htbrutremise),2) AS htbrutremise,
(case a3.remise_type when 1 then (case a3.remise_taux when 0 then 0 else round(sum(a1.htbrutremise*a3.remise_taux/100),2) end) else round(sum(round(a1.htbrutremise*100/a2.total_doc,2)*a3.remise_taux/100),2) end) * (case a3.remise_natu when 1 then -(1) else 1 end) AS montantremise_pied,
round(sum(a1.htbrutremise)+((case a3.remise_type when 1 then (case a3.remise_taux when 0 then 0 else round(sum(a1.htbrutremise*a3.remise_taux/100),2) end) else round(sum(round(a1.htbrutremise*100/a2.total_doc,2)*a3.remise_taux/100),2) end) * (case a3.remise_natu when 1 then -(1) else 1 end)),2) AS montant_avec_remise_pied,
a1.catcompta_cleunik AS catcompta_cleunik,
a1.pc_cleunik AS pc_cleunik,
a1.taxes_cleunik AS taxes_cleunik,
0 as nombredelignes
from _vue_cdecli_ht_00 a1 left join _vue_cdecli_ht_00_inter a2 using(commercial_cleunik) left join _cdecli_entetes a3 using(commercial_cleunik) where a1.commercial_cleunik=10007262 and a3.remise_genr=2 group by a1.catcompta_cleunik,a1.pc_cleunik,a1.taxes_cleunik,a1.commercial_cleunik,a2.total_doc
union all
select a1.commercial_cleunik AS commercial_cleunik,
0 as pourcentage,
0 as total_doc,
round(sum(a1.htbrutremise),2) AS htbrutremise,
(case a3.remise_type when 1 then (case a3.remise_taux when 0 then 0 else sum(a1.htbrutremise*a3.remise_taux/100) end) else sum(a3.remise_taux*a1.nombredelignes) end) * (case a3.remise_natu when 1 then -(1) else 1 end) AS montantremise_pied,
round(sum(a1.htbrutremise)+((case a3.remise_type when 1 then (case a3.remise_taux when 0 then 0 else sum(a1.htbrutremise*a3.remise_taux/100) end) else sum(a3.remise_taux*a1.nombredelignes) end) * (case a3.remise_natu when 1 then -(1) else 1 end)),2) as montant_avec_remise_pied,
a1.catcompta_cleunik AS catcompta_cleunik,
a1.pc_cleunik AS pc_cleunik,
a1.taxes_cleunik AS taxes_cleunik,
a1.nombredelignes as nombredelignes
from _vue_cdecli_ht_00 a1 left join _vue_cdecli_ht_00_inter a2 using(commercial_cleunik) left join _cdecli_entetes a3 using(commercial_cleunik) where a1.commercial_cleunik=10007262 and a3.remise_genr=1 group by a1.catcompta_cleunik,a1.pc_cleunik,a1.taxes_cleunik,a1.commercial_cleunik,a1.nombredelignes,a2.total_doc; |
Partager