CODEWITHSUNDEEP
sqlsql-servert-sql
Blixter
Blixter

Reputation: 326

Increment and then break and start over again based on a value in another column

I want to increment a column and then stop and start again based on a value in another column.

For e.g:

I have a table:

CustomerID  YearMonth   True_False  
9000        2013-01-01   0
9001        2013-02-01   0
9002        2013-03-01   0
9003        2013-04-01   0
9004        2013-05-01   0
9005        2013-06-01   1
9006        2013-07-01   0
9007        2013-08-01   0
9008        2013-09-01   0
9009        2013-10-01   1
9010        2013-11-01   0

i want to achieve this:

CustomerID  YearMonth   True_False  Sequence
9000        2013-01-01   0            1
9001        2013-02-01   0            2
9002        2013-03-01   0            3
9003        2013-04-01   0            4 
9004        2013-05-01   0            5
9005        2013-06-01   1            0
9006        2013-07-01   0            1
9007        2013-08-01   0            2
9008        2013-09-01   0            3
9009        2013-10-01   1            0
9010        2013-11-01   0            1

So this is based on True_False column. When True_False is 0 then break and start over again. Sequence value for ( True_False = 0 ) doesn't need to be 0 could be NULL as well.

Upvotes: 1

Views: 517

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Try this:

SELECT CustomerID, YearMonth, True_False,
       CASE 
          WHEN True_False = 1 THEN 0
          ELSE ROW_NUMBER() OVER (PARTITION BY True_False, grp 
                                  ORDER BY YearMonth) 
       END AS Sequence
FROM (      
   SELECT CustomerID, YearMonth, True_False,
          ROW_NUMBER() OVER (ORDER BY YearMonth) - 
          ROW_NUMBER() OVER (PARTITION BY True_False 
                             ORDER BY YearMonth) AS grp
   FROM mytable ) AS t
ORDER BY YearMonth

The query calculates field grp which identifies islands of consecutive records having the same True_False value. Using this field in an outer query we can enumerate the records contained inside these islands and thus get the required sequence number.

Demo here

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270011

You want a row number inside a group of adjacent rows. But you need to define the group. It appears to be a cumulative sum of true_false.

So:

select t.*, row_number() over (partition by grp order by year_month) - 1
from (select t.*, sum(true_false) over (order by year_month) as grp
      from t
     ) t;

Upvotes: 5

Related Questions