Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6
This is the forum to discuss the Wrox book Beginning T-SQL with Microsoft SQL Server 2005 and 2008 by Paul Turley, Dan Wood; ISBN: 9780470257036
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 13th, 2011, 02:55 AM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Performing Calculations with User Define Function

I know the problem here is me and so I need to know how to get this right, please!

I created these two tables to illustrate what I am trying to accomplished.

I am trying to calculate the total amount from the tblSupportCharges table using the CustomerID as an Input Parameter.
The problem is my calculations are dead wrong.

Here are the two tables:

Code:
CREATE TABLE tblTechSupport (
CustomerId	INT IDENTITY(1,1)NOT NULL,
CONSTRAINT PK_tblTechSupport PRIMARY KEY (CustomerId),
CompanyName VARCHAR(50)  NOT NULL,	
CompanyLoc  VARCHAR(50)  NOT NULL,	
CallDate	DATE         NOT NULL,		   						
RepName		CHAR(50)     NOT NULL
);

CREATE TABLE tblSupportCharges(
CustomerId     INT          NOT NULL,
CONSTRAINT FK_tblSupCharges FOREIGN KEY (CustomerId)
REFERENCES tblTechSupport (CustomerId), 		
[SupportHours] DECIMAL(9,2) NULL,
CostPerHour    MONEY        NULL,	
MilesTraveled  DECIMAL(9,1) NULL,
CostPer_Mile   MONEY        NULL, 
PartsCosts     MONEY        NULL,
LaborCharges   MONEY	    NULL
);

INSERT INTO tblSupportCharges(CustomerId,SupportHours,CostPerHour,MilesTraveled,CostPer_Mile)
VALUES(1,5,1,1,1);
Using the values I inserted, I created this function to get the total from the tblSupportCharges table but the results are not accurate: Here is the function: Note I made the numbers simple just for troubleshooting.

Code:
Create Function dbo.CalulateTotal (@CustomerId INT) 
RETURNS MONEY 
As 
BEGIN 
DECLARE @GetTotalCost MONEY         
SELECT  @GetTotalCost = (Sum(SupportHours * CostPerHour) + (Sum(MilesTraveled * CostPer_Mile)))
FROM tblSupportCharges                 
Return(@GetTotalCost) 
End 
Go 
Select dbo.CalulateTotal(1)
Thanks everyone!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
user name using time function ravi951 Beginning PHP 0 September 12th, 2011 03:29 AM
Cannot define a user profile rpz79 BOOK: Beginning Drupal 2 February 1st, 2011 05:40 AM
user function attempt demac43 Excel VBA 3 November 8th, 2006 04:59 PM
User Defined Function niravp SQL Server 2000 7 November 29th, 2004 01:18 PM



All times are GMT -4. The time now is 11:58 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.