drix76
asked on
Get 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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Just take the code that I posted, substitute the table name, and run it in a query window in Management Studio. It will create a stored procedure, which you then can call using for example:
GetLastDays 12
or
GetLastDays @cnt = 12
GetLastDays 12
or
GetLastDays @cnt = 12
ASKER