p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 (http://p2p.wrox.com/forumdisplay.php?f=433)
-   -   Performing Calculations with User Define Function (http://p2p.wrox.com/showthread.php?t=85664)

NovicePGM2011 November 13th, 2011 03: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:


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)))
FROM tblSupportCharges               
Select dbo.CalulateTotal(1)

Thanks everyone!

All times are GMT -4. The time now is 06:11 AM.

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