oracle sql running total range

Question

I have two tables tab_a as

   SUB_ID AMOUNT
    1     10
    2     5
    3     7
    4     15
    5     4

2 table tab_b as

    slab_number slab_start slab_end 
    1             12          20
    2             21          25
    3             26          35

slab_start will always be 1 more than slab_end of previous slab number

If I run the running total for tab_a my result is

 select sub_id , sum(amount)  OVER(ORDER BY sub_id) run_sum
from tab_a

sub_id run_sum
  1     10
  2     15
  3     22
  4     37
  5     41

I need to SQL query to check which slab_NUMBER if run_sum is less than first slab_number from then it should be Zero , if run_sum is more than last slab number then blank except the row which crosses the limit . Expected result is

  sub_id run_sum slab_number
      1     10    0
      2     15    1
      3     22    2
      4     37    3
      5     41    NULL

I have tried this .

First find the running sum which crosses the limit i. e last slab_end

        select min(  run_sum ) 
              from  (select sub_id , sum(amount)  OVER(ORDER BY sub_id) run_sum
from tab_a ) where run_sum>=35

then use below query

select sub_id,
       run_sum,
       case
         when run_sum <
              (select SLAB_START from tab_b where slab_number = '1') then
          0
         when run_sum = 37 then
          (select max(slab_number) from tab_b)
         when run_sum > 37 then
          NULL
         else
          (select slab_number
             from tab_b
            where run_sum between SLAB_START and slab_end)
       end slab_number
  from (select sub_id, sum(amount) OVER(ORDER BY sub_id) run_sum from tab_a)

is there any other way to improve.

Answer 1

I think this is basically a left join with a default value:

select a.*,
       (case when a.run_sum < bb.min_slab_num then 0
             else b.slab_num
        end) as slab_num
from (select sub_id,
             sum(amount) over (order by sub_id) as run_sum
      from tab_a
     ) a left join
     tab_b b
     on a.run_sum between slab_start and slab_end cross join
     (select min(slab_start) as min_slab_start
      from tab_b
     ) bb;

Answer 2

Somewhat strange requirement :) Use some analytic functions and case when's. Row_number when you need to find something first, max() over() and sum() over() when you need information from over rows:

with  
  a as (
    select sub_id, row_number() over (order by sub_id) rn, 
           sum(amount) over (order by sub_id) rs 
      from tab_a),
  b as (select tab_b.*, max(slab_number) over () msn from tab_b )
select sub_id, rs, 
       case when sn is null and row_number() over (partition by sn order by sub_id) = 1 
            then msn else sn 
       end sn
  from (
    select sub_id, rs, max(msn) over () msn,
           case when slab_number is null and rn = 1 then 0 else slab_number end sn
      from a left join b on rs between slab_start and slab_end)

_{dbfiddle demo}

Answer 3

you could try this:

select a.sub_id , sum(a.amount)  OVER(ORDER BY a.sub_id) run_sum
 ,case when b.slab_number=1 then 0 else  lag(b.slab_number,1) over (order by a.sub_id)end slab_number
from tab_a a
left join tab_b b on a.SUB_ID = b.slab_number