6

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?

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
user3197575
  • 269
  • 5
  • 12
  • I have a number of variations on ROW_NUMBER() looking the first time the row and Active was Zero and the second row being a non-zero but it did not come correct out as in many cases. As the active can have been 1 in some month and other not, that is why I am keen on some comparision technique for just current and previous month. – user3197575 Aug 09 '15 at 10:50

2 Answers2

2

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]

shA.t
  • 15,232
  • 5
  • 47
  • 95
1

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.

Bulat
  • 6,494
  • 1
  • 25
  • 47
  • 1
    Thank you for your suggestion.Do you think there is a way way to implement LAG and LEAD function for this problem?Your solution is good, however the problem on my side is that I will have many million of rows. That is why I am interested in just comparing two months, the previous month's column to current month's record. I like your solution because it gave some more insight as to how I can solve other problems with your strategy. Thank you Bulat. – user3197575 Aug 09 '15 at 11:44
  • now @shA.t updated his answer with correct conditions, does it work for you? – Bulat Aug 10 '15 at 07:39