Need to outp a value based on criteria in data 2

[Michael57]

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

 

[bborissov]

...
SUM(CASE WHEN PMCATGT.COSTTYPE = 'LAB'
         THEN 0
         ELSE PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM)
         END) AS COSTDIF
...

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

and may I suggest using NOT IN? It expands to ORs or ANDs so is syntactically identical, but is a darn sight easier to read:

PMCATGT.CONTRACT <> 'MAIN' And PMCATGT.CONTRACT <> 'ADM' And PMCATGT.CONTRACT <> 'TRN' And PMCATGT.CONTRACT <> 'TRV'

PMCATGT.CONTRACT NOT IN ('MAIN', 'ADM', 'TRN', 'TRV')

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[Michael57]

What if I had other items that would require a 0 output.
How can I set up multiple cases?

 

[tran008]

...
SUM(CASE WHEN PMCATGT.COSTTYPE = 'LAB' THEN 0
WHEN PMCATGT.COSTTYPE = 'OtherItem' THEN 0
WHEN PMCATGT.COSTTYPE = 'OtherItem' THEN 0
ELSE PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM)
END) AS COSTDIF
...

 

[ESquared]

SUM(CASE WHEN PMCATGT.COSTTYPE IN ('LAB', 'OtherItem', 'OtherItem2') THEN 0
ELSE PMCATGT.CURCOSTHM-PMCATGT.ACTCOSTHM)
END) AS COSTDIF


[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]