Solved

sql 2005 trend query.

Posted on 2008-06-20
8
278 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore 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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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