Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

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.
0
AkAlan
Asked:
AkAlan
  • 2
  • 2
2 Solutions
 
SharathData EngineerCommented:
Alter the function like this.
ALTER FUNCTION [dbo].[FN_LABORCOSTS] 
              (@JobNumber CHAR(5)) 
RETURNS MONEY 
AS 
  BEGIN 
    DECLARE  @TotalAmount MONEY 
     
    SELECT @TotalAmount = SUM(ts.LaborAmount + ISNULL(f.FringeCost,0)) 
      FROM TimeSheets ts 
           LEFT JOIN (  SELECT TsKey, 
                          SUM(FringeCost) FringeCost 
                     FROM Fringe 
                 GROUP BY TsKey) f 
             ON ts.TsKey = f.TsKey 
     WHERE ts.JobNumber = @JobNumber 
     
    RETURN @TotalAmount 
  END

Open in new window

0
 
Anthony PerkinsCommented:
Or just simply (no points please):
ALTER FUNCTION [dbo].[FN_LABORCOSTS] (@JobNumber CHAR(5))

RETURNS MONEY

AS

BEGIN 
     
RETURN (
   SELECT   SUM(ts.LaborAmount + ISNULL(f.FringeCost, 0))
   FROM     TimeSheets ts
            LEFT JOIN (SELECT   TsKey,
                                SUM(FringeCost) FringeCost
                       FROM     Fringe
                       GROUP BY TsKey) f ON ts.TsKey = f.TsKey
   WHERE    ts.JobNumber = @JobNumber 
     )
END

Open in new window

0
 
Anthony PerkinsCommented:
Sharath_123,

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

Feel free to object.

Anthony
0
 
AkAlanAuthor Commented:
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.
0
 
SharathData EngineerCommented:
acperkins -  Not an issue. :) Expected a split, but that's ok.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now