USE [JCS] GO /****** Object: UserDefinedFunction [dbo].[GetLaborTotal] Script Date: 12/28/2009 14:57:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Jeff Hibbs -- Create date: December 23, 2009 -- Description: Returns Labor Total from JCPROD -- ============================================= ALTER FUNCTION [dbo].[GetLaborTotal] ( -- JCPROD cId to be passed into function @tcJobNo char(6) ) RETURNS Numeric(12,2) AS BEGIN DECLARE @nRetVal Numeric(12,2) IF @tcJobNo IS NULL OR LEN(LTRIM(@tcJobNo)) = 0 SET @nRetVal = 0.00 ELSE BEGIN DECLARE @nTOTOH Numeric(12,2) ;with cte_Info as (select jcprod.cjobno, jcprod.ctype, jcprod.nhours, CASE WHEN JCRate.nregular_rate>0 THEN JCRate.nregular_rate ELSE DefaultRates.nregular_rate END as nRegular_Rate, CASE WHEN JCRate.novertime_rate>0 THEN JCRate.novertime_rate ELSE DefaultRates.novertime_rate END as nOvertime_Rate from dbo.JCProd Left JOIN dbo.DefaultRates DefaultRates ON jcprod.crate_type = DefaultRates.ctype_of_time Left JOIN dbo.JCRate JCRate ON jcprod.crate_type = JCRate.ctype_of_time and jcprod.cJobNo = JCRate.cJobNo ), cte_Summary as (select cJobNo, sum(CASE WHEN ctype = 'REG' THEN nregular_rate * nHours ELSE nOvertime_Rate * nHours END) as TotalLabor from cte_Info group by cJobNo) -- Get sum of labor SET @nTOTOH =(select TotalLabor from cte_Summary where cte_Summary.cjobno = @tcJobNo) -- Return the total of hours multiplied by rate SET @nRetVal = ISNULL(@nTOTOH, 0) END RETURN @nRetval ENDTIA