How to split row value according to specific column?-CodePudding

I have Table A and Table B and I'm having trouble getting the fulfilled qty in Table B where sum up of Fulfilled qty is equal to Available field in Table A.

For Item ID 1, the available qty is 99 in Table A and in Table B this Item ID 1 have different rack, with 99 qty, it only able to fulfill 60 qty for Rack A and remaining 39 qty for Rack B.

Table A

ID   Available
1    99
2    5

Table B

ID  Rack  Required 
1    A    60    
1    B    102    
1    C    8    
2    A    10

Desired Results

ID   Rack Required    Fulfilled 
1    A    60          60    
1    B    102         39    
1    C    8           0    
2    A    4           4
2    B    2           1

I have tried using query below but seems not able to get the expected results

SELECT ID,
       RACK,
       REQUIREDQTY,
       SUM(FULFILLEDQTY) OVER (ORDER BY ID,
                                        RACK,
                                        rows between unbounded preceding and 1 preceding) AS FULFILLEDQTY
FROM TABLEB
     LEFT JOIN TABLEA ON TABLEB.ID = TABLEA.ID;

CodePudding user response：

use sum(Required) over (...) and check against Available

select A.ID, B.Rack, B.Required,
       Fulfilled = case when sum(B.Required) over (partition by A.ID order by B.Rack) 
                        <=   A.Available
                        then B.Required
                        when sum(B.Required) over (partition by A.ID order by B.Rack) 
                        -    B.Required <= A.Available
                        then A.Available 
                        -    sum(B.Required) over (partition by A.ID order by B.Rack) 
                             B.Required
                        else 0
                        end
from   A
       inner join B on A.ID = B.ID
order by A.ID, B.Rack

db<>fiddle demo

EDIT : due to change of structure (additional column rack)

select A.ID, A.rack, B.bin, B.Required,
       Fulfilled = case when sum(B.Required) over (partition by A.ID, A.rack 
                                                       order by B.bin) 
                        <=   A.Available
                        then B.Required
                        when sum(B.Required) over (partition by A.ID, A.rack 
                                                       order by B.bin) 
                        -    B.Required 
                        <=   A.Available
                        then A.Available 
                        -    sum(B.Required) over (partition by A.ID, A.rack
                                                       order by B.bin) 
                             B.Required
                        else 0
                        end
from   A
       inner join B on A.ID = B.ID and A.rack = B.rack
order by A.ID, A.rack,  B.bin