>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 > SELECT @nTOTOH = 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 > >END >>
;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 where JcProd.cJobNo = @tcJobNo ) select @nRetVal = coalesce( sum(CASE WHEN ctype = 'REG' THEN nregular_rate * nHours ELSE nOvertime_Rate * nHours END,0) from cte_InfoIn other words, simplify it a bit and select only necessary info into cte, not everything.