drix76
asked on
Get values from especific dates from database
Hello all!
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...
Please help!!!
Thanks in advance!
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...
Please help!!!
Thanks in advance!
What SQL platform and version are you using and do you want the function in SQL or ASP?
ASKER
SQL Server 2005.
I prefer in ASP, because I don't know much about SQL Programming... But if it is easy to implement or if you could explain me how to do it, its fine!
I'll be very grateful anyway!
Thanks in advance!
I prefer in ASP, because I don't know much about SQL Programming... But if it is easy to implement or if you could explain me how to do it, its fine!
I'll be very grateful anyway!
Thanks in advance!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, I would suggest getting the data out correctly from SQL server side either via my provided code or function there. This way you will not have to bring back extraneous data to your ASP page only to have to filter out one row per month then take average. At least IMHO.