I’m trying to create segments of date based on a flag.
Below is the table I currently have order by start_date,end_date.
| ID | start_date | end_date | flag |
|---|---|---|---|
| 9659 | 5/18/2012 | 5/18/2012 | 0 |
| 9659 | 5/18/2012 | 5/18/2012 | 0 |
| 9659 | 6/8/2012 | 6/8/2012 | 0 |
| 9659 | 6/8/2012 | 6/8/2012 | 0 |
| 9659 | 8/25/2012 | 8/25/2012 | 1 |
| 9659 | 8/27/2012 | 8/27/2012 | 0 |
| 9659 | 10/27/2012 | 10/27/2012 | 1 |
I’d like to create a column named ‘segment’. This column should be incremented whenever flag=1. How to achieve it? Below is the expected result.
| ID | start_date | end_date | flag | segment |
|---|---|---|---|---|
| 9659 | 5/18/2012 | 5/18/2012 | 0 | 1 |
| 9659 | 5/18/2012 | 5/18/2012 | 0 | 1 |
| 9659 | 6/8/2012 | 6/8/2012 | 0 | 1 |
| 9659 | 6/8/2012 | 6/8/2012 | 0 | 1 |
| 9659 | 8/25/2012 | 8/25/2012 | 1 | 2 |
| 9659 | 8/27/2012 | 8/27/2012 | 0 | 2 |
| 9659 | 10/27/2012 | 10/27/2012 | 1 | 3 |
Advertisement
Answer
Use a cumulative sum:
select t.*,
sum(flag) over (partition by id order by start_date rows between unbounded preceding and current row) + 1 as seqnum
from t;
This assumes that you want a separate enumeration per id.