Below is code that will give me a ranked count the number of calls (in our IVR call table) for a particular BTN (business telephone number). However I seem to be forced to use fixed dates in the RANGE_N expression. Because I want to rank calls over a long period of time and I want to count thier ranking within a 30 day window, I need to partition by a variable moving 30 day window looking backward from the call in the current record.
Any ideas on how to make this happen? I get an error stating that I can not use variables if I try putting BETWEEN end_dt - 30 AND end_dt
Here is code that works, but for a fixed window only.
Count(BTN) 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
Hoping someone can help!
Thanks - Steve
Can you provide a DDL and some test data as inserts?
And explain with the given data your expected results.
Below is the desired output. The 'Rank' column is what I need to derive from the other data. In this case I am ranking the current call based on the number of calls that preceded it in a prior '7' day window.
Ex: the top call is ranked as '6' because it has 5 other calls preceding it within a 7 day window of date of the current call. Notice how the ranking changes relative to the date of the 'current' call.
I can derive this by joining the data to itself, but because I track millions of calls I risk running out of spool space. The Rank & Partition functions get me very close to achieving this, however I am unable to specify a partition that is variable relative to the current call.
Ideas? Thanks for your help!
This is probably too late to help SteveD but hopefully it will help others. I needed to pass variable partition values to the PARTITION BY RANGE_N function. I used the DBC.SYSEXECSQL function. It worked like a charm for me.