CODEWITHSUNDEEP
sql-server
Sami
Sami

Reputation: 3976

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

Upvotes: 2

Views: 62

Answers (2)

BICube
BICube

Reputation: 4681

DECLARE @days int = 60;
DECLARE @MaxDay INT; 
SELECT @MaxDay = MAX(DaysVisited) FROM YourTable;
SELECT *
FROM YourTable
WHERE DaysVisited BETWEEN @days AND 
      CASE 
          WHEN @days=60 THEN 90
          ELSE @MaxDay
       END

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44316

Your logic would not be possible, a column cannot be below 60 and above 90 at the same time. Try this instead:

DECLARE @days int = 60

SELECT * 
FROM 
  yourtable
WHERE
  DaysVisited between 
    case when @days < 30 then 0 else @days-30 end and @days

Upvotes: 3

Related Questions