Comparing current Month and previous Month's rows on a column, SQL Server 2012

Question

I need some guidance and help with a question I am not entirely sure how to solve in SQL Server 2012. I think LAG and LEAD functions could be useful but I am not sure.

This is what my data looks right now:

=========================================
YearMonth   LocationCode    Active      
=========================================
201405      123              0  
201406      123              2  
201409      211              1
201410      211              0
201411      214              0
201412      214              3

We have a YearMonth column that shows how the status looked like for each locationCode and an Active int that represents a quality for each LocationCode

Objective:

My objective is to compare the LocationCode for for the current YearMonth (let's call it 201406) and the previous Yearmonth (let's call it 201405):

An example :

=========================================
YearMonth   LocationCode    Active      
=========================================
201405      123              0  
201406      123              2  

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 Active.

If the current month's row Active 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).

An example is provided below:

==================================================
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  

How can I solve this problem?

Answer 1

I think you can use a query like this:

SELECT *,
    CASE 
        WHEN Active <> 0 AND 
             ISNULL(LAG(Active) OVER (PARTITION BY LocationCode ORDER BY YearMonth), 0) = 0 THEN 1 
        ELSE 0 
    END As New
FROM yourTable;

[SQL Fiddle Demo]

Answer 2

You can do this with ROW_NUMBER() OVER like this:

WITH RankedCodesHistory AS (
 SELECT 
   YearMonth,
   LocationCode,
   Active,
   ROW_NUMBER() OVER (PARTITION BY LocationCode, CASE WHEN Active > 0 THEN 1 ELSE 0 END 
                    ORDER BY LocationCode, YearMonth, Active) rn
 FROM CodesHistory)
SELECT 
  YearMonth,
  LocationCode,
  Active,
  CASE WHEN Active > 0 AND rn = 1 THEN 1 ELSE 0 END AS New
FROM RankedCodesHistory

SQL Fiddle

I have extended your data sample in the Fiddle to demonstrate what will happen if Active goes back to zero and becomes positive second time --- in this case code above will not set corresponding row as new.