Get values from especific dates from database

drix76 used Ask the Experts™
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!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

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


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!
Chief Technology Officer
Most Valuable Expert 2011

Since you have SQL Server 2005, this (see code snippet) should work for you.  It doesn't require a function as you can simply call this SQL from ASP directly passing a different top end value (i.e., 6 instead of 12).

Here is how it works:
+over analytical statement allows you to partition data without having to group overall query, which allows you to get calculations based on a group of records on each individual record; therefore, you maintain all the different values in each record and don't have to worry about figuring out which aggregate to use.  In your case, I partitioned the data on year/month.  Now within this grouping of data, I ordered records by the dt_date value descending; therefore, the first record for each month is the last day.  If that makes sense.
+row_number() function does the operation on the grouping just mentioned.  It numbers the records in order and so as I said the first row should be last day; therefore, you will see in the outer query we can now filter on row = 1.
+now with all this in a derived table, we can take the avg()
+to limit what we average I have used datediff function to calculate the number of months dt_date is from today.  records in July will be 0 months; June, 1; May, 2; etc.  Therefore, the datediff values between 1 and 12 represent data from last full twelve months.

Hopefully that helps.

select avg(vl_value) as avg_value
from (
select *
, row_number() over (partition by year(dt_date), month(dt_date) order by dt_date desc) as row
from your_table
) t
where row = 1
   and datediff(mm, dt_date, getdate()) between 1 and 12

Open in new window

Kevin CrossChief Technology Officer
Most Valuable Expert 2011

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial