View Single Post
  #1 (permalink)  
Old November 13th, 2011, 03:55 AM
NovicePGM2011 NovicePGM2011 is offline
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