Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to outp a value based on criteria in data 2

Status
Not open for further replies.

Michael57

Technical User
Nov 15, 2005
131
CA
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
 
Code:
...
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
 
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]
 
What if I had other items that would require a 0 output.
How can I set up multiple cases?
 

...
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
...
 
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]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top