>SELECT jcmast.cjobno, > CASE WHEN ctype = 'REG' THEN nregular_rate * nHours ELSE nOvertime_Rate * nHours END as nTotal from (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 = jcmast.cjobno) Derived >from dbo.jcmast >>
;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) select c.TotalLabor, M.* from cte_Summary c inner join JCMast M on c.cJobNo = M.cJobNo -- this is our final result