Work around to simulate a variable Partition in RANGE_N

Database
Fan

Work around to simulate a variable Partition in RANGE_N

Hi  --

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

Tags (3)
3 REPLIES
Supporter

Re: Work around to simulate a variable Partition in RANGE_N

Can you provide a DDL and some test data as inserts?

And explain with the given data your expected results.

Fan

Re: Work around to simulate a variable Partition in RANGE_N

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!








CALL_ID END_DT END_TM TN Rank
099201210312215 2012-10-31 22:18:34 4033404100    6
953201210291241 2012-10-29 12:42:29 4033404100    5
683201210261355 2012-10-26 13:55:57 4033404100    5
372201210261355 2012-10-26 13:55:45 4033404100    4
891201210241858 2012-10-24 18:58:18 4033404100    3
432201210241752 2012-10-24 17:52:46 4033404100    2
234201210211557 2012-10-21 15:58:28 4033404100    3
655201210161457 2012-10-16 14:57:57 4033404100    2
656201210161449 2012-10-16 14:49:21 4033404100    1
387201210021430 2012-10-02 14:30:31 4033404100    2
484201210021417 2012-10-02 14:17:58 4033404100    1

Re: Work around to simulate a variable Partition in RANGE_N

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.