Uniquely identify sets of sequential values

Database

Uniquely identify sets of sequential values

Hello,

I'm trying to add a column the table below that is a unique identifier of sequential dates.  Stand alone dates would also have a unique identifier.  I've been able to get as far as seeing if a row is part of a 2+ day streak using coalesce, over() and preceding/following... but can't think of a way to tell the streaks apart.

Columns: MemberID, visitDate, streakID

Initial Data:

xx1, 2015-01-01

xx1, 2015-01-02

xx1, 2015-01-03

xx1, 2015-01-06

xx1, 2015-01-07

xx2, 2015-01-01

xx2, 2015-01-05

xx2, 2015-01-10

xx3, 2015-01-01

xx3, 2015-01-01

xx3, 2015-01-02

Ideal Result:

Initial Data:

xx1, 2015-01-01, streak1

xx1, 2015-01-02, streak1

xx1, 2015-01-03, streak1

xx1, 2015-01-06, streak2

xx1, 2015-01-07, streak2

xx2, 2015-01-01, streak3 (or streak1)

xx2, 2015-01-05, streak4 (or streak2 if partitioning by memberID)

xx2, 2015-01-10, streak5 (or streak3)

xx3, 2015-01-01, streak6 (or streak1)

xx3, 2015-01-01, streak6 (or streak1)

xx3, 2015-01-02, streak6 (or streak1)

1 REPLY

Re: Uniquely identify sets of sequential values

Hi,

One Method can be



CREATE TABLE TEMP1 AS(

SELECT MemberID, visitDate,CALENDAR_DATE,CASE WHEN CALENDAR_DATE IS NULL THEN 1 ELSE 0 END AS TEMP FROM SYS_CALENDAR.CALENDAR

LEFT JOIN VISITER_INFO

ON visitDate=CALENDAR_DATE

)WITH DATA PRIMARY_INDEX(SAME_AS_VISITER_INFO);

SELECT MemberID,visitDate,1+CSUM(TEMP,CALENDAR_DATE ASC,visitDate ASC)  AS streakID FROM TEMP1

QUALIFY TEMP=0;  --THIS WILL GIVE DESIRED RESULT  BUT  streakID MAY NOT CONTINUES... TO GET IT CONTINUES ADD ONE MORE STEP  BY GENERATING RANK ON streakID