title: calculate difference for a column moving from one value to another sql
body: <p>I have the following two tables:</p>
  
  <p>table1</p>
  
  <pre><code>item_id, sent_from, received_by, month, value
  </code></pre>
  
  <p>table2</p>
  
  <pre><code>item_id, sender, value, month
  </code></pre>
  
  <p>Values for sent_from, received_by, sender are the same type and format.
  Data is like the following:</p>
  
  <p>table1</p>
  
  <pre><code>values ('1234','A1111','K0000',1,5)
  values ('1234','K0000','K0000',2,5)
  values ('1234','K0000','Z0000',3,10)
  </code></pre>
  
  <p>table2</p>
  
  <pre><code>values ('1234','A1111',5,1)
  values ('1234','K0000',10,2)
  values ('1234','Z0000',20,3)
  </code></pre>
  
  <p>I'm trying to reconcile between table1 values and the difference in values from month to preivous month in table2. For instance:
  value for month 3 in table1 should be 10 which is the same value that comes from the difference between the value from month 3 - 2 in table2. So 20 - 10 = 10. Then 10 - 5 = 5 then 5 - 0 should be 5 and so on. The impact_value from below should be the same as the values in the table1.</p>
  
  <p>The following is the code I have but it's not working propery and is not computing the right values:</p>
  
  <pre><code>select sls.item_id,
         sls.sent_from,
         sls.received_by,
         sum(case when prd.sender = sent_from then -1 * prd.value
                  else prd.value
             end) impact_value,
         sls.mnth
  from   table2 prd
         inner join tabl1 sls on (prd.item_id = sls.item_id
                                  and (prd.sender = sls.sent_from
                                       or (prd.sender = sls.received_by
                                           )))
  group by sls.item_id,
           sls.sent_from,
           sls.received_by,
           sls.mnth;
  </code></pre>
  
  <p>However, the impact_value field is not populated with the right values like in the logic.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>insert the following into <strong>table1</strong>:</p>
  
  <pre><code>values ('1234','K0000','Z0005',4,40)
  values ('1234','BBBBB','ZZZZZ',4,60)
  </code></pre>
  
  <p>and insert the following into <strong>table2</strong>:</p>
  
  <pre><code>values ('1234','K0000',60,4)
  values ('1234','BBBBB',120,4)
  </code></pre>