sql 2005 trend query.

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.
romeiovasuAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
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
 
chapmandewCommented:
can you give an example of what you'd want the output to look like?
0
 
romeiovasuAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
chapmandewCommented:
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
 
romeiovasuAuthor Commented:
if i want to create the samething as a funtion how can i do that.
0
 
romeiovasuAuthor Commented:
thank you
0
 
romeiovasuAuthor Commented:
thank you
0
 
chapmandewCommented:
very welcome.
0
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.