I need to make a function that gets the past 12 records of the last day avaiable of each month from a date.
I need to sum them then divide it for 12 to get the avarage of the period.
I have a table with 3 colums (dt_date, vl_value, id_prod) which records a day by day historical (except weekends and holidays), so the last day of a month is not necessarily the last day (eg: Last day for May/2009 was not 31 but 29)
Example: Today is 7/14/2009. The values I need to get are:
(vl_value in 6/30/2009 + vl_value in 5/29/2009 + vl_value in 4/30/2009 + vl_value in 3/31/2009 + vl_value in 2/27/2009 + vl_value in 1/30/2009 + vl_value in 12/31/2008 + vl_value in 11/28/2008 + vl_value in 10/30/2008 + vl_value in 9/30/2009 + vl_value in 8/29/2009)/12
I may have that function for 12, 6 and 24 periods...
Thanks in advance!