Reset rank when date interval exceeds limit

Database
N/A

Reset rank when date interval exceeds limit

I've been bashing my brain against this and can't find a way to rank rows that works, though I'm sure this should be pretty simple.

What I'm needing to do is reset rank when the interval between the current rows date column exceeds a specific interval (eg. 3 days or 30 days) from the previous date, within the same partition. Can anyone provide some guidance?

An example of this with normal rank would be:

ID          CID                Date           Rank
--------------------------------------------------------------------------
122556655 G54470785 2013-01-18 15:55:00.0 1
122556655 G54470811 2013-01-18 16:33:00.0 2
122556655 G54469852 2013-03-02 17:32:00.0 3
122556655 G54470733 2013-03-03 14:40:00.0 4
122556655 G54470759 2013-03-03 15:18:00.0 5
122556698 G54469853 2013-03-02 17:33:00.0 1
122556698 G54470734 2013-03-03 14:42:00.0 2
122556698 G54470760 2013-03-03 15:19:00.0 3

The desired answer set would be a reset rank, when the difference between dates exceeds 30 days, as in:

ID          CID                Date           Rank
--------------------------------------------------------------------------
122556655 G54470785 2013-01-18 15:55:00.0 1
122556655 G54470811 2013-01-18 16:33:00.0 2
122556655 G54469852 2013-03-02 17:32:00.0 1
122556655 G54470733 2013-03-03 14:40:00.0 2
122556655 G54470759 2013-03-03 15:18:00.0 3
122556698 G54469853 2013-03-02 17:33:00.0 1
122556698 G54470734 2013-03-03 14:42:00.0 2
122556698 G54470760 2013-03-03 15:19:00.0 3
2 REPLIES
N/A

Re: Reset rank when date interval exceeds limit

I may have found a a solution with the following, this appears to rank the way I wantd. Could someone sanity-check this for me?

RANK() OVER(
PARTITION BY ID
ORDER BY "DATE"
RESET WHEN (cast("date" as date) - min(cast("date" as date)) over(partition by ID order by "date" rows between 1 preceding and 1 preceding )) >= 30

) AS "RANK"

Re: Reset rank when date interval exceeds limit

Yup, it would work perfectly.