Getting the Rolling Average

Question

I have a list of Dates which appear in a dropdown box, I need to get the 12 month rolling average for each month. The list of dates are in the column called date_end.

SELECT total_subs, cust_region, cust_phone, date_end
FROM table_customer
where date_end >=  date_end - '13 months'

date_end is saved as an Ansidate.

date_end    
2014-08-31
2014-07-31
2014-06-30
2014-05-30

When a user selects a value from the dropdown list of end dates the query should return the chosen values between that end date and the end date minus 13 months

Please edit your post to indicate the data types for the columns mentioned in your query and to include some sample data. And - rolling average of what column? Thanks. — Bob Jarvis - Слава Україні, Nov 30 '14 at 22:30
getting the rolling average of total_subs which is an integer, grouping by the region, phone number and date_end — user3399863, Nov 30 '14 at 22:58

score 0 · Answer 1 · answered Dec 01 '14 at 04:01

Can you do something like this in Ingres? Forgive me if not.

SELECT total_subs, cust_region, cust_phone, date_end, (
    (select avg(total_subs)
     from table_customer tc2
     where tc2.date_end > tc.date_end - '12 months'
    ) as rolling_average
FROM table_customer tc
where date_end >=  date_end - '13 months'

Getting the Rolling Average

1 Answers1