View Single Post
November 13th, 2011, 03:55 AM
NovicePGM2011
Authorized User
 Points: 53, Level: 1
 Activity: 0%

Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
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!