title: Converting lump sums to transactions
body: <p>I have a database that tracks the size of claims.</p>
  
  <p>Each claim has fixed information that is stored in <code>claim</code> (such as <code>claim_id</code> and <code>date_reported_to_insurer</code>).</p>
  
  <p>Each month, I get a report which is added to the table <code>claim_month</code>. This includes fields such as <code>claim_id</code>, <code>month_id</code> [101 is 31/01/2018, 102 is 28/02/2018, etc]  and <code>paid_to_date</code>.</p>
  
  <p>Since most claims don't change from month to month, I only add a record for claim_month when the figure has changed since last month. As such, a claim may have a June report and an August report, but not a July report. This would be because the amount paid to date increased in June and August, but not July.</p>
  
  <p>The problem that I have now is that I want to be able to check the amount paid each month. </p>
  
  <p>Consider the following example data:</p>
  
  <pre><code>+----------------+----------+----------------+--------------+
  | claim_month_id | claim_id | month_id       | paid_to_date |
  +----------------+----------+----------------+--------------+
  | 1              | 1        | 6              | 1000         |
  +----------------+----------+----------------+--------------+
  | 5              | 1        | 7              | 1200         |
  +----------------+----------+----------------+--------------+
  | 7              | 2        | 6              | 500          |
  +----------------+----------+----------------+--------------+
  | 12             | 1        | 9              | 1400         |
  +----------------+----------+----------------+--------------+
  | 18             | 2        | 8              | 600          |
  +----------------+----------+----------------+--------------+
  </code></pre>
  
  <p>If we assume that this is all of the information regarding claim 1 and 2, then that would suggest that they are both claims that occurred during June 2018. Their transactions should look like the following:</p>
  
  <pre><code>+----------------+----------+----------------+------------+
  | claim_month_id | claim_id | month_id       | paid_month |
  +----------------+----------+----------------+------------+
  | 1              | 1        | 6              | 1000       |
  +----------------+----------+----------------+------------+
  | 5              | 1        | 7              | 200        |
  +----------------+----------+----------------+------------+
  | 7              | 2        | 6              | 500        |
  +----------------+----------+----------------+------------+
  | 12             | 1        | 9              | 200        |
  +----------------+----------+----------------+------------+
  | 18             | 2        | 8              | 100        |
  +----------------+----------+----------------+------------+
  </code></pre>
  
  <p>The algorithm I'm using for this is</p>
  
  <pre><code>SELECT claim_month_id, 
         month_id, 
         claim_id, 
         new.paid_to_date - old.paid_to_date AS paid_to_date_change, 
  FROM   claim_month AS new 
         LEFT JOIN claim_month AS old 
                ON new.claim_id = old.claim_id 
                   AND ( new.month_id &gt; old.month_id 
                          OR old.month_id IS NULL ) 
  GROUP  BY new.claim_month_id 
  HAVING old.month_id = Max(old.month_id)
  </code></pre>
  
  <p>However this has two issues:</p>
  
  <ol>
  <li>It seems really inefficient at dealing with claims with multiple
  records. I haven't run any benchmarking, but it's pretty obvious.</li>
  <li>It doesn't show new claims. In the above example, it would only show lines 2, 3 and 5.</li>
  </ol>
  
  <p>Where am I going wrong with my algorithm, and is there a better logic to use to do this?</p>