Hi all,
using Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Sample data:
drop table inflationData;
drop table dataA;
drop table dataB;
create table inflationData(
sample_id number,
fy number,
rate number
);
insert into inflationData (sample_id,fy,rate)
values (1,2011,1.03);
insert into inflationData (sample_id,fy,rate)
values (1,2012,1.04);
insert into inflationData (sample_id,fy,rate)
values (1,2013,1.06);
insert into inflationData (sample_id,fy,rate)
values (1,2014,1.078);
insert into inflationData (sample_id,fy,rate)
values (1,2015,1.10);
create table dataA(
sample_id number,
sub_id number,
item_desc nvarchar2(20),
ava_perc number
);
insert into dataA (sample_id,sub_id,item_desc,ava_perc)
values (1,301,'Item A',0.5);
insert into dataA (sample_id,sub_id,item_desc,ava_perc)
values (1,302,'Item B',1);
insert into dataA (sample_id,sub_id,item_desc,ava_perc)
values (1,303,'Item C',0.8);
create table dataB(
sub_id number,
fy number,
total number
);
insert into dataB(sub_id,fy,total)
values (301,2011,20);
insert into dataB(sub_id,fy,total)
values (301,2012,20);
insert into dataB(sub_id,fy,total)
values (301,2013,20);
insert into dataB(sub_id,fy,total)
values (302,2011,40);
insert into dataB(sub_id,fy,total)
values (302,2012,50);
insert into dataB(sub_id,fy,total)
values (302,2013,60);
insert into dataB(sub_id,fy,total)
values (303,2011,80);
insert into dataB(sub_id,fy,total)
values (303,2012,90);
insert into dataB(sub_id,fy,total)
values (303,2013,100);
select a.sample_id, a.sub_id, item_desc, i.fy, b.total , ava_perc , i.rate
from dataA a
join dataB b on a.sub_id = b.sub_id
join inflationData i on a.sample_id = i.sample_id and b.fy = i.fy
order by 1,2,3,4
The data shows as:
SAMPLE_ID SUB_ID ITEM_DESC FY TOTAL AVA_PERC RATE
1 301 Item A 2011 20 0.5 1.03
1 301 Item A 2012 20 0.5 1.04
1 301 Item A 2013 20 0.5 1.06
1 302 Item B 2011 40 1 1.03
1 302 Item B 2012 50 1 1.04
1 302 Item B 2013 60 1 1.06
1 303 Item C 2011 80 0.8 1.03
1 303 Item C 2012 90 0.8 1.04
1 303 Item C 2013 100 0.8 1.06
select a.sample_id, a.sub_id, item_desc, i.fy, sum(b.total * ava_perc * i.rate) amt
from dataA a
join dataB b on a.sub_id = b.sub_id
join inflationData i on a.sample_id = i.sample_id and b.fy = i.fy
group by a.sample_id, a.sub_id, item_desc, i.fy
order by 1,2,3,4
With calculation shows as:
SAMPLE_ID SUB_ID ITEM_DESC FY AMT
1 301 Item A 2011 10.3
1 301 Item A 2012 10.4
1 301 Item A 2013 10.6
1 302 Item B 2011 41.2
1 302 Item B 2012 52
1 302 Item B 2013 63.6
1 303 Item C 2011 65.92
1 303 Item C 2012 74.88
1 303 Item C 2013 84.8
How can i use the last group of data for each sub_id (like Item A total = 20 and ava_perc = 0.5) on the missing years 2014 and 2015?
Getting the inflation rate from the inflation table of the years 2014 = 1.078 and 2015 = 1.10.
SAMPLE_ID SUB_ID FY AMT
1 301 2011 10.3
1 301 2012 10.4
1 301 2013 10.6
1 301 2014 (20 * 0.5 * 1.078)
1 301 2015 (20 * 0.5 * 1.10)
the same shoud be apply to other sub_id
1 302 2011 41.2
1 302 2012 52
1 302 2013 63.6
1 302 2014 X
1 302 2015 X
1 303 2011 65.92
1 303 2012 74.88
1 303 2013 84.8
1 303 2014 X
1 303 2015 X
I tried last_value without success as:
select i.sample_id,
a.sub_id,
i.fy,
(LAST_VALUE(b.total * ava_perc * i.rate) OVER(PARTITION BY i.sample_id, a.sub_id ORDER BY i.fy)) as amt
from dataA a
join dataB b on a.sub_id = b.sub_id
right join inflationData i on a.sample_id = i.sample_id and b.fy = i.fy
order by 1,2,3,4
I hope this is clear.
Thanks!