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.jcmastTIA