Link to home
Start Free TrialLog in
Avatar of drix76
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!
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

What SQL platform and version are you using and do you want the function in SQL or ASP?
Avatar of drix76
drix76

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!
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.