case stament in where clause

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  June 2, 2006 at 2:30 pm

  #171065

  DECLARE @sp_type int

  SET @sp_type =5

  SELECT * FROM

  sales

  WHERE

   case WHEN @sp_type= 5 THEN Cur_date>DATEADD(mm,-1,getdate())

   WHEN @sp_type=6 THEN Cur_date>DATEADD(mm,-2,getdate())

   ELSE Cur_date>DATEADD(mm,-3,getdate())

   END

   

  I am trying select the data from a table based on the condition @sp_type.

  If its 5 I need to get the data for last one month starting from cur_date and if its 6 i need to get last two months data starting from the cur_date and if it's anything other than 5 and 6 then I need  to get the data for last 3 month.

  How can I implement this?

   

  Thanks.

• andrewkane17

  SSCertifiable

  Points: 7529

  More actions

  June 2, 2006 at 2:41 pm

  #641530

  This should work

   

  DECLARE @sp_type int

  SET @sp_type =5

  SELECT * FROM

  sales

  WHERE Cur_date >

   case WHEN @sp_type= 5 THEN DATEADD(mm,-1,getdate())

   WHEN @sp_type=6 THEN DATEADD(mm,-2,getdate())

   ELSE DATEADD(mm,-3,getdate())

   END

• stax68

  SSChampion

  Points: 11711

  More actions

  June 3, 2006 at 6:32 am

  #641565

  If you're going to do that, why not:

  declare @sp_type int

  set @sp_type = 5

   

  declare @datesarg datetime

  set @datesarg = case @sp_type 

  when 5 then dateadd(mm,-1,getdate())

  when 6 then dateadd(mm,-2,getdate())

  else dateadd(mm,-3,getdate())

  end

  select * from

  sales

  where Cur_date > @datesarg

  that way the CASE statement can't affect the execution plan (or reuse of an execution plan)

  Tim Wilkinson

  "If it doesn't work in practice, you're using the wrong theory"
  - Immanuel Kant