Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get especific dates from database

Posted on 2009-07-14
6
Medium Priority
?
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

609 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