All Forums Database
SteveD 4 posts Joined 11/12
04 Dec 2012
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

ulrich 816 posts Joined 09/09
04 Dec 2012

Can you provide a DDL and some test data as inserts?
And explain with the given data your expected results.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

SteveD 4 posts Joined 11/12
10 Dec 2012

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

alexanh 1 post Joined 04/15
09 Sep 2015

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.

You must sign in to leave a comment.