title: Determine if column is increasing or decreasing
body: <p>I have data table that has column <code>ORDER</code> that is supposed to indicate if the values are increasing or decreasing and another column <code>ORDER_BASIS</code>. However, data in <code>ORDER</code> is often incorrect at the first place so I am trying to determine the correct order using <code>ORDER_BASIS</code>.</p>
  <p>Here's what the table looks like:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">ORDER</th>
  <th style="text-align: center;">ORDER_BASIS</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">8</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">12</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">5</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">1</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">10</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I am trying to achieve this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: center;">ORDER</th>
  <th style="text-align: center;">ORDER_BASIS</th>
  <th style="text-align: center;">CORRECT_ORDER</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">8</td>
  <td style="text-align: center;">INCREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  <td style="text-align: center;">INCREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">12</td>
  <td style="text-align: center;">DECREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">5</td>
  <td style="text-align: center;">DECREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">DECREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">1</td>
  <td style="text-align: center;">DECREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">10</td>
  <td style="text-align: center;">INCREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  <td style="text-align: center;">INCREASING</td>
  </tr>
  <tr>
  <td style="text-align: center;">INCREASING</td>
  <td style="text-align: center;">16</td>
  <td style="text-align: center;">INCREASING</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>First column may use <code>ORDER</code> then the following rows should determine if it's increasing or decreasing. If value did not increase or decrease then remain with it's current status until there's a change in value.</p>
  <p>My current logic uses LAG and LEAD:</p>
  <pre><code>SELECT
      LEAD (ORDER_BASIS, 1, 0) AS NEXT_BASIS,
      LAG (ORDER_BASIS, 1, 0) AS PREV_BASIS
  FROM 
      DATA_TABLE
  </code></pre>
  <p>Then created a condition but cannot get it to work correctly</p>
  <pre><code>CASE 
      WHEN CAST(PREV_BASIS AS int) = 0  
           OR (CAST(PREV_BASIS AS int) &gt;= CAST(ORDER_BASIS AS int) 
               AND CAST(NEXT_BASIS AS int) &lt;= CAST(ORDER_BASIS AS int))   
          THEN ORDER_BASIS 
          ELSE 'OPPOSITE_DIRECTION' 
  END AS CORRECT_ORDER
  </code></pre>
  <p>Using SQL Server 2014</p>