title: In SQL, split an amount among multiple rows
body: <p>In our application we are using SQL Server 2012. I come across a situation, where I am not able to come up with a single query (without loop) that will solve my purpose. Let me explain it.</p>
  
  <p>I have a <strong>Travel</strong> table -</p>
  
  <pre><code>ID       State      Days_Count   Remaining_Days
  1         AL          20            -
  2         AL          2             -
  3         AL          14            -
  </code></pre>
  
  <p>I have another table for <strong>Travel_Sum</strong></p>
  
  <pre><code>State      Days_Sum   Max_Limit    Exceeding_Amt
  AL           36          12             24
  </code></pre>
  
  <p>There are more entries for other States. What I am trying is to write a single query that will update remaining days with <strong>Exceeding_Amt</strong>. In this example value of Exceeding_Amt column is 24. So in the first table, I'm trying to update value 20 (max value of days_Count) against remaining days, 2 against second row and (24-20-2) 2 against last row.</p>
  
  <p>Basically I have to split exceeding amount (ie 24) among rows of first table where state matches. So to split 24, I want to update remaining_days of first row as 20. Because I can not update with a value that is more than days_count. So I have 4 days left.
  For second row, Days_count is 2. So I have updated this row with 2 (max value which I'm allowed to enter). In third row I have updated Remaining_Days column value with remaining 2 days.</p>
  
  <p><strong>Desired output :</strong></p>
  
  <pre><code>ID       State      Days_Count   Remaining_Days
  1         AL          20            20
  2         AL          2             2
  3         AL          14            2
  </code></pre>
  
  <p>This question might be confusing. If so, please let me know. I'll try my best to give a proper understanding.</p>