Solved

Get especific dates from database

Posted on 2009-07-14
6
254 Views
Last Modified: 2012-05-07
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!
0
Comment
Question by:drix76
  • 2
6 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 500 total points
ID: 24851204
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

as
 

set nocount on
 

declare

  @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

end
 

select Total = @sum

Open in new window

0
 

Author Comment

by:drix76
ID: 24853624
Thank you for your answer, but, I very newbee to SQL, so how do I implement this to try?
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 25549325
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
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now