Bucket Filling SQL query CTE
<p>I want to achieve the  below output from the given input tables.</p>

<p>Input Table (Bucket to be filled)</p>

<pre><code>ID | FullCapacity | CurrentAmount    
---+--------------+--------------
B1 |     100      |     0    
B2 |      50      |     0
B3 |      70      |     0
</code></pre>

<p>Input Table (Filler Table)</p>

<pre><code>        ID | Filler            
        ---+-------
        F1 | 90              
        F2 | 70          
        F3 | 40    
        F4 | 20 
</code></pre>

<p>Output table should have below showing filling process. </p>

<pre><code>ID | FullCapacity | CurrentAmount       
---+--------------+--------------
B1 |    100       |    90        
B2 |     50       |     0    
B3 |     70       |     0
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50
B3 |     70       |    10
---+--------------+--------------
B1 |    100       |   100      
B2 |     50       |    50    
B3 |     70       |    50
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50    
B3 |     70       |    70
</code></pre>

<p>I am trying to fill this one by one from filler to bucket. Can we do this without using cursor?</p>

<p>Please see that we can have multiple types of buckets for example red bucket, blue bucket and red filler, blue filler. Red filler to go to red bucket, blue filler to blue and so on. </p>

<p>Thank you</p>