Performing Calculations with User Define Function

NovicePGM2011 · November 13th, 2011, 03:55 AM

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!