Syntax help with SQL Function
Posted on 2011-03-06
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]
DECLARE @TotalAmount money
SELECT @TotalAmount = (SELECT sum(ts.LaborAmount)
FROM TimeSheets ts
WHERE ts.JobNumber = @JobNumber)
Here is how I use it in the Stored procedure:
ALTER PROCEDURE [dbo].[spSelectJobs]
dbo.fn_LaborCosts(j.JobNumber) 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.