>Naomi,
>
>Both of these solutions work but I'm afraid they don't solve my ultimate problem. I want to be able to return the total labor cost per job. The is returning the total labor cost per time entry. The parent table is JCMAST and contains a field called cJobNo which is the key this field is also found in JCPROD. I was trying to retrieve the cJobNo from JCMAST and the labor total from JCPROD below but I keep getting the following error message:
>
>Msg 156, Level 15, State 1, Line 13
>Incorrect syntax near the keyword 'from'.
>
>
>

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

>
>TIA
>Jeff

Jeff,

The whole point of CTE or Derived table is to use it as a separate entity - it doesn't allow you to access underlying tables anymore.

So, once again:

;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