All Forums Database
Dolm 15 posts Joined 05/11
30 Mar 2013
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

 

Dolm 15 posts Joined 05/11
30 Mar 2013

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"

 

KS42982 137 posts Joined 12/12
01 Apr 2013

Yup, it would work perfectly.

You must sign in to leave a comment.