samedi 27 juin 2015

sql server multi case where clause

I want to retrieve rows where DaysVisted are between two values, e.g if @days is 90 then fetch records which has DaysVisited between 90 and 60, if @days is 60 then fetch records which has DaysVisited between 60 and 30.

Following query is not giving me correct records:

declare @days int
set @days = 60
select * from table where
DaysVisited >=
CASE                
   when  (@days = 90) then 
   when  (@days = 60) then @days       
   when  (@days = 0) then 0
END

I want like this:

declare @days int
set @days = 60
select * from table where

CASE                
   when  (@days = 90) then DaysVisited >= 90 and DaysVisited <= 60 
   when  (@days = 60) then DaysVisited >= 60 and DaysVisited <= 30 
   when  (@days = 0) then 0
END

Aucun commentaire:

Enregistrer un commentaire