CASE in the WHERE clause

• Ulysses_Infinity

  SSCrazy

  Points: 2588

  More actions

  April 8, 2010 at 4:23 am

  #99128

  Hi,

  I want to apply a conditional where criteria depending on the evaluation of two dates, but can't seem to get my code to work. My query is as follows:

  [declare @EditionDate datetimeset @EditionDate = '2000/01/01'

  select

  'CAPId'= pr.pr_id,

  'Manufacturer'= cv.CVehicle_ManText,

  'Model'= cv.CVehicle_ModText,

  'Derivative'= cv.CVehicle_DerText,

  'Basic'= pr.pr_basic,

  'VAT'= pr.pr_vat,

  'Delivery'= pr.pr_delivery,

  'EffectiveDate' = convert(varchar(20),pr.pr_effectivefrom,103),

  'TotalPrice'= ROUND(pr.pr_basic + pr.pr_vat + pr.pr_delivery,0)

  from

  dead_car..nvdprices pr

  left join dead_car..CAPVehicles cv on pr.pr_id = cv.CVehicle_ID

  case when cv.cvehicle_derintroduced <= @EditionDate

  then (pr.pr_effectiveFrom <= @EditionDate and (pr.pr_EffectiveTo is null or pr.pr_EffectiveTo > @EditionDate))

  else pr.pr_EffectiveFrom =(select min(pr2.pr_effectivefrom)

  from dead_car..nvdprices pr2

  where pr2.pr_id = pr.pr_id

  group by pr2.pr_id )

  end

  So, if cv.cvehicle_derintroduced <= @EditionDate I want the where criteria to be (pr.pr_effectiveFrom <= @EditionDate and (pr.pr_EffectiveTo is null or pr.pr_EffectiveTo > @EditionDate)) else use the following where criteria:

  pr.pr_EffectiveFrom =(select min(pr2.pr_effectivefrom)

  from dead_car..nvdprices pr2

  where pr2.pr_id = pr.pr_id

  group by pr2.pr_id )

  Any help would be greatly appreciated.

  Thanks in advance . . . Jason

• vaibhav.tiwari

  SSCommitted

  Points: 1976

  More actions

  April 8, 2010 at 4:46 am

  #1148130

  This can only be achieved by dynamic query...

  becasue your condition operator is not fix, as for one condition it is <= and for another it is =

  Case in where works to decide right hand side value not for whole where condition.

  For ex.

  SELECT * FROM table

  WHERE ID = ( Case WHEN @temp > 0 THEN 10 ELSE 11 )

  Vaibhav K Tiwari
  To walk fast walk alone
  To walk far walk together