title: Take different actions depending on value presence in table
body: <p>I have table where I add changes in values of particular columns in other tables:</p>
  <p>UpdatesLogTable:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>TableName</th>
  <th>ColumnFilter</th>
  <th>FilterName</th>
  <th>FilterValue</th>
  <th>Difference</th>
  <th>Date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td>Apple</td>
  <td>4</td>
  <td>0</td>
  <td>1/13/2021</td>
  </tr>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td>Pear</td>
  <td>5</td>
  <td>0</td>
  <td>1/13/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>When I insert new records based on last update date of the tables I have issue determining the conditions for inserting.</p>
  <p>For example, yesterday my inventory of <code>Apples</code> was equal to <code>4</code> and today it is <code>5</code>. So, in my UpdatesLog table I will insert <code>Fruits-FruitName-Apple-5</code>. In the column <code>Difference</code> I will calculate the difference between yesterday's and today's value which is <code>1</code>. This will be ok, since I already have Apple as value in FilterName column.</p>
  <p>But if on my last update in <code>Fruits table</code> I add new <code>FruitName - Peach</code>, <strong>how am I supposed to check that this value does not exist and add it with default <code>Difference value equal to 0</code>?</strong></p>
  <p>Below is the last update on the 14-th of Jan:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Fruits</th>
  <th>FruitsInventory</th>
  <th>LastUpdateDate</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Apple</td>
  <td>5</td>
  <td>1/14/2021</td>
  </tr>
  <tr>
  <td>Pear</td>
  <td>9</td>
  <td>1/14/2021</td>
  </tr>
  <tr>
  <td>Peach</td>
  <td>10</td>
  <td>1/14/2021</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>And this is the result I am expecting:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>TableName</th>
  <th>ColumnFilter</th>
  <th>FilterName</th>
  <th>FilterValue</th>
  <th>Diff</th>
  <th>Date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td>Apple</td>
  <td>4</td>
  <td>0</td>
  <td>1/13/2021</td>
  </tr>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td>Pear</td>
  <td>5</td>
  <td>0</td>
  <td>1/13/2021</td>
  </tr>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td><strong>Apple</strong></td>
  <td><strong>5</strong></td>
  <td><strong>1</strong></td>
  <td><strong>1/14/2021</strong></td>
  </tr>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td><strong>Pear</strong></td>
  <td><strong>9</strong></td>
  <td><strong>4</strong></td>
  <td><strong>1/14/2021</strong></td>
  </tr>
  <tr>
  <td>Fruits</td>
  <td>FruitName</td>
  <td><strong>Peach</strong></td>
  <td><strong>10</strong></td>
  <td><strong>0</strong></td>
  <td><strong>1/14/2021</strong></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>This is my code in case I don't check if the fruit name exists:</p>
  <pre><code>INSERT INTO UpdatesLogTable(TableName, ColumnFilter, FilterName, FilterValue, [Difference], [Date])
  SELECT [TableName]
      , [ColumnFilter]
      , [FilterName]
      , [FilterValue]
      , [Difference]
      , LUP as [Date]
  FROM (
      SELECT tr.TableName
      , tr.ColumnFilter
      , tr.FilterName
      , tr.FilterValue
      , tr.[FilterValue] - ult.[FilterAbsValue] AS [Difference]
      , LUP
      FROM (SELECT
                  'Fruits' as [TableName]
                  , 'FruitName' as [ColumnFilter]
                  ,  FruitName as [FilterName]
                  , FruitsInventory AS [FilterValue]
                  , MAX(LastUpdateDate as date) as LUP
              FROM Fruits  
              WHERE 1=1
              ) tr
      RIGHT JOIN UpdatesLogTable ult 
      ON tr.TableName = ult.TableName and tr.ColumnFilter = ult.ColumnFilter 
      WHERE hus.LastUpdateDate = (SELECT MAX(CAST(LastUpdateDate as date)) FROM UpdatesLogTable)
  ) a
  </code></pre>
  <p>I was thinking of LEFT or RIGHT join but this will work only if I know if the fruits variety is increasing or decreasing.</p>