Solved

sql 2005 trend query.

Posted on 2008-06-20
8
279 Views
Last Modified: 2010-04-21
i have a data.
sales
25,000
60,000
12,000

i want to write a query to show the trend for those sales. or a function so that it automatically can trend. so that i can use that function for everything.

i want like (sales*noofdaysinamonth)/presentday

so that i can show the trend for sales.
0
Comment
Question by:romeiovasu
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21833892
can you give an example of what you'd want the output to look like?
0
 

Author Comment

by:romeiovasu
ID: 21834414
sales      Trend
$25,000.00       $37,500.00
$60,000.00       $90,000.00
$12,000.00       $18,000.00

here is the sample.
it actually done like this. trend = (sales*noofdaysinamonth)/dayofthemonth.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834463
Great...this should do it:


select (Sales * day(dateadd(d, -1, cast(cast(month(dateadd(m, 1, getdate())) as varchar(2)) + '/1/' + cast(year(getdate()) as varchar(4)) as datetime)))) / day(getdate())
FRom YOurTableName
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:romeiovasu
ID: 21834497
if i want to create the samething as a funtion how can i do that.
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 21834547
this should do it:

CREATE FUNCTION udf_SalesTrend ( @SalesValue MONEY )
RETURNS MONEY
AS BEGIN
    DECLARE @RetVal MONEY
    SELECT  @RetVal = ( @SalesValue * DAY(DATEADD(d, -1,
                                                  CAST(CAST(MONTH(DATEADD(m, 1, GETDATE())) AS VARCHAR(2))
                                                  + '/1/'
                                                  + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS DATETIME))) )
            / DAY(GETDATE())
    RETURN ( @RetVal )
   END
GO

THen, you can just call it like this:

select Sales, Trend = dbo.udf_SalesTrend(Sales)
FROM YourTableName
0
 

Author Closing Comment

by:romeiovasu
ID: 31469290
thank you
0
 

Author Comment

by:romeiovasu
ID: 21834676
thank you
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834689
very welcome.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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