I'm working on a project and i'm facing some difficulties. I'am able now to retreive data but i must use it in a calulation.
My actual code is :
with tree as
(
select AA.PORTEFEUILLE, AA.fonds, round(AA.nbreparts , 7) parts, opcvm, level lev
, SYS_CONNECT_BY_PATH(fonds, '/') Path
, connect_by_root(fonds) leaf
, connect_by_root(AA.nbreparts) detenus
from dbo.chiffres AA
connect by fonds= prior opcvm
)
select distinct tree.Portefeuille, lev, path,
round(detenus/cc.nombre_parts_av_vl,7) weight, CC.frais
from tree
inner join dbo.histo_portef_fp_group CC on leaf=CC.portefeuille
and CC.date_fp = to_date('25/03/2016','dd/mm/yyyy')
The resulat i'm getting now is:
Portefeuille || LEV || Path || weight || frais
5000 || 1 || /010 || 0,73% || 100
5000 || 2 || /001/010 || 0,39% || 300
5000 || 2 || /013/010 || 0,09% || 200
5000 || 3 || 211/013/010 || 2,79% || 150
But the final resulat that i want is:
Portefeuille || LEV || Path || total
5000 || 3 || 211/013/010 || 0,73%*100 + 0,73%*0,09%*200 + 0,73%*0,09%*2,79%*150
5000 || 2 || /001/010 || 0,73%*100 + 0,73%*0,39%*300
I'm using 2 tables:
CREATE TABLE chiffres(
Portefeuille varchar2(15),
Fonds varchar2(15),
nbreparts number(8,2),
opcvm varchar2(15)
);
and
CREATE TABLE histo_portef_fp_group(
Portefeuille varchar2(15),
date_fp DATE,
nombre_parts_av_vl number(8,2),
frais number(8,2)
);
and an example of data to get the result above:
INSERT INTO chiffres(Portefeuille,Fonds,nbreparts,opcvm)
VALUES('5000','010',380, null);
INSERT INTO chiffres(Portefeuille,Fonds,nbreparts,opcvm)
VALUES('104827','211',3800,'013');
INSERT INTO chiffres(Portefeuille,Fonds, nbreparts,opcvm)
VALUES('104823','013',3,'010');
INSERT INTO chiffres(Portefeuille,Fonds, nbreparts,opcvm)
VALUES('104823','001',302,'010');
and
INSERT INTO histo_portef_fp_group(Portefeuille,date_fp, nombre_parts_av_vl, frais)
VALUES('010', DATE '2016-03-25',52224, 100);
INSERT INTO histo_portef_fp_group(Portefeuille,date_fp, nombre_parts_av_vl, frais)
VALUES('013',DATE '2016-03-25',3175, 200);
INSERT INTO histo_portef_fp_group(Portefeuille,date_fp, nombre_parts_av_vl, frais)
VALUES('211',DATE '2016-03-25',136354, 150);
INSERT INTO histo_portef_fp_group(Portefeuille,date_fp, nombre_parts_av_vl, frais)
VALUES('001',DATE '2016-03-25',76449, 300);
Thank you for your help.