Link to home
Start Free TrialLog in
Avatar of AkAlan
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.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.
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sharath_123,

My apologies, as you can see I did try, unfortunately it seems the author overlooked my request.

Feel free to object.

Anthony
Avatar of AkAlan
AkAlan

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.