All Forums Analytics
prakash5801 8 posts Joined 02/10
04 Jun 2010
How to read only two rows at a time by ordering it

HOw could I take an average of two values by arranging the data in ascending order using SQL. For example

For the Col "Var1" with data

TrackId Var1
1 1.2
2 1.1
3 1.5
4 1.6
5 1.3

The result should be first order the sorted data as var1=[1.1, 1.2, 1.3, 1.5, 1.6] and then take an average of two values, i.e.,
The result will be evaluated as:

Result
-------------
(1.1+1.2)/2
(1.2+1.3)/2
(1.3+1.5)/2
(1.5+1.6)/2

Regards,

Prakash

jainpee 1 post Joined 05/09
04 Jun 2010

You can try using ordered analytical function ie AVG with OVER clause ie

AVG (var1) over (order by var1 ROWS BETWEEN current row PRECEDING AND
1 FOLLOWING)

SteveD 4 posts Joined 11/12
26 Nov 2012

I'm trying to rank calls through our IVR based on the number of calls that occurred before it in the previous 30 days (ie is this the 1st, 2nd, 3rd .... call in the last 30 days?) 
 
I can do this for a fixed 30 day window, but I need to rank calls wihtin a rolling 30 day window over a long period of time.  I've tried using the RANK () OVER(PARTITION .... approach, however it will not accept a variable partition.
SELECT
call_ID ,
end_dt,
end_tm,
CUST_TN,
RANK() OVER (PARTITION BY BTN, [Rolling 30 day partition here] ORDER BY end_dt, end_tm ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) Prev_Rec
FROM ivr.vivr_calls
WHERE end_dt BETWEEN '2012-09-01' AND '2012-10-31'
QUALIFY end_dt BETWEEN '2012-10-01' AND '2012-10-31'
 
Any ideas?
 
Thanks  - Steve
 

You must sign in to leave a comment.