title: Loop through the records and find the nearest match and write the result to a new table
body: <p>All data is initially contained in one table <code>#T1_rate_trans</code>
  At the moment I am starting my loop by creating two separate temp tables <code>#trn_CR1</code> and <code>#trn_DR1</code> where there are still CR records to be matched</p>
  <pre><code>DECLARE @count int
  SET @count = 0
  WHILE (select count(*) from #T1_rate_trans where tran_type = 'CR' and balance &lt;&gt;0)&gt;0
  BEGIN
      /****  drop temp table ****/
      IF OBJECT_ID ('tempdb..#trn_CR1') IS NOT NULL DROP TABLE #trn_CR1;
      IF OBJECT_ID ('tempdb..#trn_DR1') IS NOT NULL DROP TABLE #trn_DR1;
      IF OBJECT_ID ('tempdb..#trn_matched1') IS NOT NULL DROP TABLE #trn_matched1;
      /**** 4. CR trans ****/
  SELECT  * 
  into #trn_CR1
  FROM #T1_rate_trans 
  WHERE balance &lt;&gt; 0  and tran_type = 'CR' 
  ORDER BY record_id,ass_num,rte_cde,rte_dif,trn_dte
      /**** 5. DR trans ****/
  CREATE TABLE #trn_DR1
  (
  Record_ID_DR int NOT NULL,
  ass_num_DR int NOT NULL,
  trn_dte_DR datetime NOT NULL,
  trn_num_DR int NOT NULL,
  trn_lin_DR int NULL,
  trn_cde_DR smallint NOT NULL,
  rte_cde_DR smallint NOT NULL,
  rte_dif_DR smallint NOT NULL,
  bal_cde_DR smallint NOT NULL,
  trn_ref_DR varchar(40) NULL,
  amount_exc_DR money NOT NULL,
  tax_DR money NOT NULL,
  amount_inc_DR money NOT NULL,
  cre_dte_tme_DR datetime NOT NULL,
  rte_yer_DR smallint NOT NULL,
  tran_type_DR varchar(2) NOT NULL,
  ABS_amount_inc_DR money NOT NULL,
  applied_amt_DR money NULL,
  balance_DR money NULL,
  record_applied_to_DR int NULL
  ) 
  ;
  -- Insert DR transactions FROM T1_Rate_trans
  INSERT INTO #trn_DR1
  (Record_ID_DR
  ,ass_num_DR
  ,trn_dte_DR
  ,trn_num_DR
  ,trn_lin_DR
  ,trn_cde_DR
  ,rte_cde_DR
  ,rte_dif_DR
  ,bal_cde_DR
  ,trn_ref_DR
  ,amount_exc_DR
  ,tax_DR
  ,amount_inc_DR
  ,cre_dte_tme_DR
  ,rte_yer_DR
  ,tran_type_DR
  ,ABS_amount_inc_DR
  ,applied_amt_DR
  ,balance_DR
  ,record_applied_to_DR
  )
  SELECT * FROM #T1_Rate_Trans 
  WHERE balance &lt;&gt; 0  and tran_type = 'DR'
  ORDER BY ass_num,rte_cde,rte_dif,trn_dte
  ;   
  </code></pre>
  <p>My first table contains CR records and looks like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Record_ID</th>
  <th style="text-align: left;">ass_num</th>
  <th style="text-align: left;">rte_cde</th>
  <th style="text-align: left;">rte_dif</th>
  <th style="text-align: left;">bal_cde</th>
  <th style="text-align: left;">amount_exc</th>
  <th style="text-align: left;">tax</th>
  <th style="text-align: left;">amount_inc</th>
  <th style="text-align: left;">cre_dte_tme</th>
  <th style="text-align: left;">rte_yer</th>
  <th style="text-align: left;">ABS_amount_inc</th>
  <th style="text-align: left;">applied_amt</th>
  <th style="text-align: left;">balance</th>
  <th style="text-align: left;">record_applied_to</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-944.6</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-944.6</td>
  <td style="text-align: left;">21/10/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">944.6</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-944.6</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">12</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-1290.58</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1290.58</td>
  <td style="text-align: left;">9/01/2004</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">1290.58</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1290.58</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">174</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-899.39</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-899.39</td>
  <td style="text-align: left;">10/11/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">899.39</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-899.39</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">175</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">23</td>
  <td style="text-align: left;">-9.75</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-9.75</td>
  <td style="text-align: left;">10/11/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">9.75</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-9.75</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">187</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-1457.85</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1457.85</td>
  <td style="text-align: left;">30/12/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">1457.85</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1457.85</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">191</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-1528.85</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1528.85</td>
  <td style="text-align: left;">25/02/2004</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">1528.85</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1528.85</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">193</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-1518.97</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1518.97</td>
  <td style="text-align: left;">15/04/2004</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">1518.97</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-1518.97</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">194</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">23</td>
  <td style="text-align: left;">-10.4</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-10.4</td>
  <td style="text-align: left;">15/04/2004</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">10.4</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-10.4</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">621</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">90</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-9</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-9</td>
  <td style="text-align: left;">1/07/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-9</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">622</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">91</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">-70.00</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-70.00</td>
  <td style="text-align: left;">1/07/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">70.00</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">-70.00</td>
  <td style="text-align: left;"></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My second table contains the DR records and looks like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Record_ID_DR</th>
  <th style="text-align: left;">ass_num_DR</th>
  <th style="text-align: left;">rte_cde_DR</th>
  <th style="text-align: left;">rte_dif_DR</th>
  <th style="text-align: left;">bal_cde_DR</th>
  <th style="text-align: left;">amount_exc_DR</th>
  <th style="text-align: left;">tax_DR</th>
  <th style="text-align: left;">amount_inc_DR</th>
  <th style="text-align: left;">cre_dte_tme_DR</th>
  <th style="text-align: left;">rte_yer_DR</th>
  <th style="text-align: left;">ABS_amount_inc_DR</th>
  <th style="text-align: left;">applied_amt_DR</th>
  <th style="text-align: left;">balance_DR</th>
  <th style="text-align: left;">record_applied_to_DR</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;">12/09/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">12/09/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">185</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">90</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;">1/07/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;"></td>
  </tr>
  <tr>
  <td style="text-align: left;">186</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">91</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">21</td>
  <td style="text-align: left;">65.62</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">65.62</td>
  <td style="text-align: left;">1/07/2003</td>
  <td style="text-align: left;">2004</td>
  <td style="text-align: left;">65.62</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">65.62</td>
  <td style="text-align: left;"></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I then need to match the records from the two tables based on a number of fields which I do via the following. They can only be included in the match if the balance or balance_DR value &lt;&gt; 0 AND the CR amount is &lt;= the DR amount AND I only want to write the first first match which is why I am using the rn = 1 criteria below.</p>
  <pre><code>/**** 6. create temp table for matched transaction ids ****/
       CREATE TABLE #trn_matched1
      (Credit_Record_ID int
      ,Debit_Record_ID int)
  
      /**** 7. insert matched transactions into temp table ****/
  INSERT INTO #trn_matched1
  SELECT t.Record_ID,t.Record_ID_DR
  FROM
  (
  SELECT 
      CR.Record_ID,CR.ass_num,cr.amount_inc, DR.Record_ID_DR,DR.ass_num_DR,DR.amount_inc_DR
      ,ROW_NUMBER() OVER (PARTITION BY CR.ass_num,rte_cde,rte_dif ORDER BY CR.ass_num,rte_cde,rte_dif ) rn
  FROM 
      #trn_CR1 CR, #trn_DR1 DR
  WHERE 
      CR.ass_num = DR.ass_num_DR
      and
      CR.rte_cde = DR.rte_cde_DR
      and
      CR.rte_dif = DR.rte_dif_DR
      and
      CR.rte_yer &lt;= DR.rte_yer_DR
      and 
      CR.ABS_amount_inc &lt;= DR.ABS_amount_inc_DR
      and 
      DR.cre_dte_tme_DR &lt;= CR.cre_dte_tme
      AND (CR.balance &lt;&gt; 0 or DR.balance_DR &lt;&gt; 0)) t
  WHERE t.rn = 1
  </code></pre>
  <p>The results from the inner SQL above, without the t.rn=1 criteria,  looks like this</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Record_ID</th>
  <th style="text-align: left;">ass_num</th>
  <th style="text-align: left;">amount_inc</th>
  <th style="text-align: left;">Record_ID_DR</th>
  <th style="text-align: left;">ass_num_DR</th>
  <th style="text-align: left;">amount_inc_DR</th>
  <th style="text-align: left;">rn</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">-944.6</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">12</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">-1290.6</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">100014</td>
  <td style="text-align: left;">2235.18</td>
  <td style="text-align: left;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">174</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-899.39</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">175</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-9.75</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">187</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-1457.9</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">3</td>
  </tr>
  <tr>
  <td style="text-align: left;">191</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-1528.9</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">4</td>
  </tr>
  <tr>
  <td style="text-align: left;">193</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-1519</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">5</td>
  </tr>
  <tr>
  <td style="text-align: left;">194</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-10.4</td>
  <td style="text-align: left;">168</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">5405.06</td>
  <td style="text-align: left;">6</td>
  </tr>
  <tr>
  <td style="text-align: left;">621</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">-9</td>
  <td style="text-align: left;">185</td>
  <td style="text-align: left;">101335</td>
  <td style="text-align: left;">9</td>
  <td style="text-align: left;">1</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I only want the first match i.e.<br>
  Record_ID 5 to Record_ID_DR 2<br>
  Record_ID 174 to Record_ID_DR 168<br>
  Record_ID 621 to Record_ID_DR 185</p>
  <p>After the match above I have a number of steps that update the balance and applied amounts in the <code>#T1_rate_trans</code> table and writes some data to another table. I then start the loop again. i.e. remove the <code>#trn_CR1</code> and <code>#trn_DR1</code> tables and recreate them as the records that match the criteria have now changed i.e. balance/balance_DR &lt;&gt; 0</p>
  <p>The problem I am encountering is that the loop becomes a continuous loop as there are instances where I have records in <code>#trn_CR1</code> that have a balance and rn =1 but there are no records in the <code>#trn_DR1</code> table that they can be matched to because the exact match criteria cannot be met - this is usually because the <code>#trn_CR1.ABS_amount_inc</code> is greater that the <code>#trn_DR1.ABS_amount_inc_DR</code> eg Record 622 from the <code>#trn_CR1</code> table and Record 186 from <code>#trn_DR1</code></p>
  <p>I know I need to put a BREAK in somewhere but for the life of me I can't work out where or how.</p>
  <p>I would appreciate any help or suggestions.</p>