How to divide a given number between the ranges as shown below?

Question

Given Number: 2111.03

T1 data:

Name	No
AAA	2111.03

T2 data:

Name	Start Range	End Range
AAA	0	100
AAA	100	130
AAA	130	155
AAA	155	99990

Expected Output: Number should go though each range and subtract by the max range, need to show the remaining number at the end.

Name	No
AAA	100
AAA	30
AAA	55
AAA	1926.03

Please help me write a query

Please help me to write a query in SQL, PLSQL.

Answer 1

I made some assumptions based on your question, as it wasn't clear. If you need adjustment, let me know. The following uses Snowflake's SQL and I added a BBB value in the data to show values that fall short of the ranges, just in case:

create or replace temp table tab1 (name string, no float) as
select name, no
from (values ('AAA',2111.03),('BBB',112) x (name,no));

create or replace temp table tab2 (name string, start_range number, end_range number) as
select name, start_range, end_range
from (values ('AAA',0,100),('AAA',100,130),('AAA',130,155),('AAA',155,99990),('BBB',0,100),('BBB',100,130),('BBB',130,155),('BBB',155,99990) x (name, start_range, end_range));

solution:

select t1.name
     , t1.no
     , sum(t2.end_range) over (partition by t2.name order by end_range, start_range rows between UNBOUNDED PRECEDING and CURRENT ROW) as max_sum
     , lag(t2.end_range) over (partition by t2.name order by end_range, start_range) as max_last
     , case when no >= max_sum then end_range-start_range
            else  
                case when no-max_last > 0 then no-max_last
                     else 0
                     end
            end as new_no
from tab1 t1
join tab2 t2
  on t1.name = t2.name;