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
| with commercialv as
(select 1 NumCom, 1000 salaire_fix from dual union
select 2,1100 from dual union
select 3,1200 from dual union
select 4,1500 from dual union
select 5,1100 from dual
),
ventev as
(select 1 Numcom,to_date('01/01/2015','dd/mm/yyyy') datv, 23 mont_vente from dual union
select 1 Numcom,to_date('02/01/2015','dd/mm/yyyy'), 2 mont_vente from dual union
select 1 Numcom,to_date('03/01/2015','dd/mm/yyyy'), 12 mont_vente from dual union
select 2 Numcom,to_date('01/01/2015' ,'dd/mm/yyyy'), 11 mont_vente from dual union
select 2 Numcom,to_date('06/01/2015' ,'dd/mm/yyyy'), 23 mont_vente from dual union
select 2 Numcom,to_date('07/01/2015' ,'dd/mm/yyyy'), 56 mont_vente from dual union
select 2 Numcom,to_date('09/01/2015' ,'dd/mm/yyyy'), 23 mont_vente from dual union
select 2 Numcom,to_date('10/01/2015' ,'dd/mm/yyyy'), 3 mont_vente from dual union
select 1 Numcom,to_date('01/02/2015' ,'dd/mm/yyyy'), 77 mont_vente from dual union
select 1 Numcom,to_date('10/02/2015' ,'dd/mm/yyyy'), 55 mont_vente from dual union
select 1 Numcom,to_date('01/03/2015' ,'dd/mm/yyyy'), 63 mont_vente from dual )
select v.Numcom,c.salaire_fix+0.05*v.total_mont_vente
from (
select sum(mont_vente) total_mont_vente,Numcom,trunc(datv,'mm') datv
from ventev
group by Numcom,trunc(datv,'mm')
)v join commercialv c on c.Numcom=v.Numcom |
Partager