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!
Who is Participating?
Göran AnderssonConnect With a Mentor Commented:
First calculate the date of the first day in this month. Then you get the value for the first record before that date. Then you move back a month and repeat until you have got the number of values that you want:
create procedure GetLastDays
  @cnt int
set nocount on
  @date datetime,
  @sum int
set @date = convert(datetime, convert(varchar(8),getdate(),120) + '01', 120)
set @sum = 0
while (@cnt > 0) begin
  select top 1 @sum = @sum + vl_value
  from TheTableWhateverItIsCalled
  where dt_date < @date
  order by dt_date desc
  set @date = dateadd(month, -1, @date)
  set @cnt = @cnt - 1
select Total = @sum

Open in new window

drix76Author Commented:
Thank you for your answer, but, I very newbee to SQL, so how do I implement this to try?
Göran AnderssonCommented:
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


GetLastDays @cnt = 12
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.