title: Assignation counting up to maximum distributing uniformly
body: <p>I have the below 2 tables:</p>
  <p>accounts</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ACCOUNT</th>
  <th>D</th>
  <th>POSTAL_CODE</th>
  <th>ASSIGN</th>
  <th>NOT_ASSIGN</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ACC1</td>
  <td>8</td>
  <td>PC1</td>
  <td>AG1</td>
  <td>null</td>
  </tr>
  <tr>
  <td>ACC2</td>
  <td>7</td>
  <td>PC1</td>
  <td>null</td>
  <td>AG2</td>
  </tr>
  <tr>
  <td>ACC3</td>
  <td>6</td>
  <td>PC2</td>
  <td>null</td>
  <td>AG2</td>
  </tr>
  <tr>
  <td>ACC4</td>
  <td>5</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  </tr>
  <tr>
  <td>ACC5</td>
  <td>4</td>
  <td>PC1</td>
  <td>null</td>
  <td>null</td>
  </tr>
  <tr>
  <td>ACC6</td>
  <td>3</td>
  <td>PC1</td>
  <td>null</td>
  <td>null</td>
  </tr>
  <tr>
  <td>ACC7</td>
  <td>2</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  </tr>
  <tr>
  <td>ACC8</td>
  <td>1</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>resources</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>POSTAL_CODE</th>
  <th>AGENCY</th>
  <th>CAPACITY</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>PC1</td>
  <td>AG1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>PC1</td>
  <td>AG2</td>
  <td>2</td>
  </tr>
  <tr>
  <td>PC2</td>
  <td>AG1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>PC2</td>
  <td>AG2</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'd like to assign to each account (prioritised by highest D) the resources for each postal_code for which there is capacity avilable. If the column 'ASSIGN' is populated, then that agency must be assigned to that account if the capacity has not yet been exhausted (if it has, the value should remain null). If the 'NOT_ASSIGN' column is populated, then that agency cannot be assigned to that account.</p>
  <p>The desired output for the above logic would look like this (the explanation column would not needed).</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ACCOUNT</th>
  <th>D</th>
  <th>POSTAL_CODE</th>
  <th>ASSIGN</th>
  <th>NOT_ASSIGN</th>
  <th>AGENCY</th>
  <th>EXPLANATION</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>ACC1</td>
  <td>8</td>
  <td>PC1</td>
  <td>AG1</td>
  <td>null</td>
  <td>AG1</td>
  <td>Must be assigned to AG1 and there's capacity</td>
  </tr>
  <tr>
  <td>ACC2</td>
  <td>7</td>
  <td>PC1</td>
  <td>null</td>
  <td>AG2</td>
  <td>null</td>
  <td>Cannot be assigned to AG2 but there's no capacity for any of the other agencies (AG1)</td>
  </tr>
  <tr>
  <td>ACC3</td>
  <td>6</td>
  <td>PC2</td>
  <td>null</td>
  <td>AG2</td>
  <td>AG1</td>
  <td>Cannot be assigned to AG2 and there's capacity for AG1 in postal code PC2</td>
  </tr>
  <tr>
  <td>ACC4</td>
  <td>5</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  <td>AG2</td>
  <td>No restrictions and there's capacity for AG2 in postal code PC2</td>
  </tr>
  <tr>
  <td>ACC5</td>
  <td>4</td>
  <td>PC1</td>
  <td>null</td>
  <td>null</td>
  <td>AG2</td>
  <td>No restrictions and there's capacity for AG2 in postal code PC1</td>
  </tr>
  <tr>
  <td>ACC6</td>
  <td>3</td>
  <td>PC1</td>
  <td>null</td>
  <td>null</td>
  <td>AG2</td>
  <td>No restrictions and there's capacity for AG2 in postal code PC1</td>
  </tr>
  <tr>
  <td>ACC7</td>
  <td>2</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  <td>AG2</td>
  <td>No restrictions and there's capacity for AG2 in postal code PC2</td>
  </tr>
  <tr>
  <td>ACC8</td>
  <td>1</td>
  <td>PC2</td>
  <td>null</td>
  <td>null</td>
  <td>null</td>
  <td>No restrictions, but the capacity has been exhausted for all agencies in PC2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Please find below the sample query for the initial tables:</p>
  <pre><code>with accounts (account, d, postal_code, assign, not_assign) as(
  select 'ACC1', 8, 'PC1', 'AG1', null from dual union all
  select 'ACC2', 7, 'PC1', null, 'AG2' from dual union all
  select 'ACC3', 6, 'PC2', null, 'AG2' from dual union all
  select 'ACC4', 5, 'PC2', null, null from dual union all
  select 'ACC5', 4, 'PC1', null, null from dual union all
  select 'ACC6', 3, 'PC1', null, null from dual union all
  select 'ACC5', 4, 'PC2', null, null from dual union all
  select 'ACC5', 4, 'PC2', null, null from dual),
  resources(pc, a, cap) as(
  select 'PC1', 'AG1', 1 from dual union all
  select 'PC1', 'AG2', 2 from dual union all
  select 'PC2', 'AG1', 1 from dual union all
  select 'PC2', 'AG2', 2 from dual)
  
  select * from resources;
  </code></pre>
  <p>PD: Not sure if the logic is too complex for queries and I would better off using a cursor.</p>