I need to output a value of 0 for COSTDIF whenever PMCATGT.COSTTYPE is = LAB in the following program but I'm not having any luck. Help is much appreciated.
Select PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE,SUM (PMCATGT.CURQTY) AS BUDGETHRS,SUM (PMCATGT.ACTQTY) AS ACTUALHRS,SUM (PMCATGT.CURCOSTHM) AS BUDGETCOST,SUM (PMCATGT.ACTCOSTHM) AS ACTUALCOST,MAX (PM.STATUS),SUM( PMCATGT.CURQTY -PMCATGT.ACTQTY) AS HRDIF,SUM(PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM) AS COSTDIF
from STDDAT.dbo.PMCATGT PMCATGT
join STDDAT.dbo.PMCONTS PM on PMCATGT.CONTRACT=PM.CONTRACT
Where (PM.STATUS=30 And PMCATGT.CONTRACT > '793' And PMCATGT.CONTRACT <> 'MAIN' And PMCATGT.CONTRACT <> 'ADM' And PMCATGT.CONTRACT <> 'TRN' And PMCATGT.CONTRACT <> 'TRV')
Group By PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE
Select PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE,SUM (PMCATGT.CURQTY) AS BUDGETHRS,SUM (PMCATGT.ACTQTY) AS ACTUALHRS,SUM (PMCATGT.CURCOSTHM) AS BUDGETCOST,SUM (PMCATGT.ACTCOSTHM) AS ACTUALCOST,MAX (PM.STATUS),SUM( PMCATGT.CURQTY -PMCATGT.ACTQTY) AS HRDIF,SUM(PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM) AS COSTDIF
from STDDAT.dbo.PMCATGT PMCATGT
join STDDAT.dbo.PMCONTS PM on PMCATGT.CONTRACT=PM.CONTRACT
Where (PM.STATUS=30 And PMCATGT.CONTRACT > '793' And PMCATGT.CONTRACT <> 'MAIN' And PMCATGT.CONTRACT <> 'ADM' And PMCATGT.CONTRACT <> 'TRN' And PMCATGT.CONTRACT <> 'TRV')
Group By PMCATGT.CONTRACT,PMCATGT.PROJECT,PMCATGT.COSTTYPE