IMG-20211006-WA0002.jpg (122.58 KB)we have to find the billvalue field based on conditions mentioned in the excel screenshot ,so i wrote a below query ,but i need to know the other better approach. with main_table as (
select
1 planid , 'planA' planname ,'10000,0.25;10000,0.20;10000,0.15;99999,0.10' usagerate, 33000 tot_usage
from dual
union
select 2 planid , 'planB' planname ,'5000,0.30;10000,0.25;15000,0.20;25000,0.15;99999,0.10' usagerate, 33000 tot_usage
from dual
union
select
3 planid , 'planC' planname ,'5000,0.25;10000,0.20;99999,0.15' usagerate, 33000 tot_usage
from dual
union
select
4 planid , 'planD' planname ,'99999,0.25' usagerate, 33000 tot_usage
from dual
union
select
5 planid , 'planE' planname ,'25000,0.25;99999,0.20' usagerate, 33000 tot_usage
from dual
),
sub_query1 as (
select a.*,
level as lvl,
regexp_substr(usagerate,'[^;]+', 1, level) ind_usage_rate
from main_table a
connect by regexp_substr(usagerate,'[^;]+', 1, level) is not null),
sub_query2 as (
select distinct b.* ,
substr(ind_usage_rate,1,instr(ind_usage_rate,',')-1) usage,
substr(ind_usage_rate,instr(ind_usage_rate,',')+1) rate
from sub_query1 b),
sub_query3 as (
select planid,planname,tot_usage,usagerate,lvl,usage,rate,
decode (usage,'99999',0,usage*rate) as used
from sub_query2
)
--select distinct a.* from sub_query3 a order by planid,lvl
,
sub_query4 as (
select distinct c.*,
sum(usage) over(partition by planid order by lvl) sum_usage,
sum(usage*rate) over(partition by planid order by lvl) sum_rate
from sub_query3 c
)
--select * from sub_query4 order by planid,lvl
,
sub_query5 as (
select d.* ,
case when sum_usage >tot_usage then
tot_usage-lag(sum_usage,1,0) over(partition by planid order by lvl)
else 0
end as remaining_usage
from sub_query4 d
)
--select * from sub_query5 order by planid,lvl
,
sub_query6 as (
select e.* ,
case when remaining_usage>0 then
remaining_usage*rate+lag(sum_rate,1,0) over(partition by planid order by lvl)
end as total_amount
from sub_query5 e
)
select planid,planname,tot_usage,usagerate,total_amount from sub_query6
where total_amount is not null