All Forums Database
SteveD 4 posts Joined 11/12
27 Nov 2012
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!
 
 

You must sign in to leave a comment.