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:
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)
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.
Create Function dbo.CalulateTotal (@CustomerId INT)
DECLARE @GetTotalCost MONEY
SELECT @GetTotalCost = (Sum(SupportHours * CostPerHour) + (Sum(MilesTraveled * CostPer_Mile)))