CODEWITHSUNDEEP
sqlsql-serversql-server-2008
T Dang
T Dang

Reputation: 157

sql case statement or if statement

Hi I am doing a simple sql report and can not wrap my head around how to do this simple case statement . a parameter is either 1 or 0 for @risk. If there is a risk then return all of the records with risks > 0 if there is no risk then return all the safe records > 0

SELECT count(b.observationid), a.NumAtRisk , a.CBICode, c.Observerid, d.CBIDescription, d.CBIcatID
FROM dbo.ObservationDetails a
JOIN dbo.Observation b
    ON a.ObservationID = b.ObservationID
join #Temp c 
    on c.Observerid = b.ObserverID
join CBI d
on a.cbiid = d.cbiID
where b.ObservationDate between @Begdate and @Enddate 


-- Parameter needs to return one or the other......................
and case when  
(@risk) = 1  then [a.NumAtRisk] > 0  
     else [a.NumSafe] > 0 

     end

Upvotes: 0

Views: 68

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Something like this:

where (@risk = 1 and a.NumAtRisk > 0) or
      (@risk = 0 and a.NumSafe > 0)

A case expression is not needed.

Upvotes: 4

Related Questions