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
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
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.
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