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

Answer 1

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'