AkAlan
asked on
Syntax help with SQL Function
I have a funtion with one parameter that returns a sum of all wages paid for a particular job from a table called TimeSheets. I use this funtion in a stored procedure but now I find I need fringe costs (Union Dues) also. For every row of wages there are four lines of fringe costs in another related table that I need to include as part of the total wages.
Here is the function I use to return labor costs against a job:
ALTER FUNCTION [dbo].[fn_LaborCosts]
(@JobNumber char(5))
RETURNS MONEY
AS
BEGIN
DECLARE @TotalAmount money
SELECT @TotalAmount = (SELECT sum(ts.LaborAmount)
FROM TimeSheets ts
WHERE ts.JobNumber = @JobNumber)
RETURN @TotalAmount
Here is how I use it in the Stored procedure:
ALTER PROCEDURE [dbo].[spSelectJobs]
AS
SELECT
JobNumber,
JobDescription,
ProposalPrice,
dbo.fn_LaborCosts(j.JobNum ber) AS TotalLabor
FROM dbo.Jobs j
The PK for the Fringe table is TsKey so for every row in the TimeSheets table there are four rows in the Fringe table with the TsKey. I need to add up all the fringe costs and add them to the total labor costs and I am not sure how to accomplish this. Thanks for any help on this.
Here is the function I use to return labor costs against a job:
ALTER FUNCTION [dbo].[fn_LaborCosts]
(@JobNumber char(5))
RETURNS MONEY
AS
BEGIN
DECLARE @TotalAmount money
SELECT @TotalAmount = (SELECT sum(ts.LaborAmount)
FROM TimeSheets ts
WHERE ts.JobNumber = @JobNumber)
RETURN @TotalAmount
Here is how I use it in the Stored procedure:
ALTER PROCEDURE [dbo].[spSelectJobs]
AS
SELECT
JobNumber,
JobDescription,
ProposalPrice,
dbo.fn_LaborCosts(j.JobNum
FROM dbo.Jobs j
The PK for the Fringe table is TsKey so for every row in the TimeSheets table there are four rows in the Fringe table with the TsKey. I need to add up all the fringe costs and add them to the total labor costs and I am not sure how to accomplish this. Thanks for any help on this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ace, I just realized what happened here. I will try and rectify it. I thought the second post was you with a cleaner solution than your first.
acperkins - Not an issue. :) Expected a split, but that's ok.
My apologies, as you can see I did try, unfortunately it seems the author overlooked my request.
Feel free to object.
Anthony