How to Rank inside a rolling partition window???

Database
Fan

How to Rank inside a rolling partition window???

I'm looking to rank the number of a specific call based on the volume of calls that precede it within a 30 day window prior to the current call.  The challenge ... I am ranking calls over a long period of time so the 30 day predeing window is not fixed.  Essentially it is a running total count on a 30 day rolling window.

This code almost works, however I need the RANGE_N section to be variable/rolling vs fixed.

SELECT

call_id,

end_dt,

end_tm,

cust_TN,

RANK() OVER (PARTITION BY BTN, RANGE_N(end_dt BETWEEN '2012-10-01' AND '2012-10-31') ORDER BY end_dt, end_tm ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) Call_Rank

FROM ivr.vivr_calls

WHERE

end_dt BETWEEN '2011-12-01' AND '2012-10-31'

QUALIFY end_dt BETWEEN '2012-01-01' AND '2012-10-31'

Hope someone can help - Thanks!

Tags (2)