title: Comparing current Month and previous Month's rows on a column, SQL Server 2012
body: <p>I need some guidance and help with a question I am not entirely sure how to solve in SQL Server 2012. I think <code>LAG</code> and <code>LEAD</code> functions could be useful but I am not sure.</p>
  
  <p>This is what my data looks right now:</p>
  
  <pre><code>=========================================
  YearMonth   LocationCode    Active      
  =========================================
  201405      123              0  
  201406      123              2  
  201409      211              1
  201410      211              0
  201411      214              0
  201412      214              3
  </code></pre>
  
  <p>We have a <code>YearMonth</code> column that shows how the status looked like for each <code>locationCode</code> and an <code>Active</code> int that represents a quality for each <code>LocationCode</code></p>
  
  <p><strong>Objective:</strong></p>
  
  <p>My objective is to compare the <code>LocationCode</code> for for the current <code>YearMonth</code> (let's call it <code>201406</code>) and the previous <code>Yearmonth</code> (let's call it <code>201405</code>): </p>
  
  <p>An example :</p>
  
  <pre><code>=========================================
  YearMonth   LocationCode    Active      
  =========================================
  201405      123              0  
  201406      123              2  
  </code></pre>
  
  <p>Basically what I am trying to figure out is how to compare the current month's row (201406) to the previous month's row (201405) on the column called <code>Active</code>.</p>
  
  <p>If the current month's row <code>Active</code> column is a non-zero and the previous month's Active was a zero, then we conclude the current month's row to be "New" (1) else (0).</p>
  
  <p>An example is provided below:</p>
  
  <pre><code>==================================================
  YearMonth   LocationCode    Active   New    
  ===================================================
  201405      123              0        0 
  201406      123              2        1
  201409      211              1        0
  201410      211              0        0
  201411      214              0        0
  201412      214              3        1  
  </code></pre>
  
  <p>How can I solve this problem?</p>