p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/)
-   BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 (http://p2p.wrox.com/book-beginning-t-sql-microsoft-sql-server-2005-2008-isbn-978-0-470-25703-6-433/)
-   -   Performing Calculations with User Define Function (http://p2p.wrox.com/book-beginning-t-sql-microsoft-sql-server-2005-2008-isbn-978-0-470-25703-6/85664-performing-calculations-user-define-function.html)

NovicePGM2011 November 13th, 2011 02:55 AM

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!


All times are GMT -4. The time now is 09:33 AM.

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