Need a Query where with case statement

• smharabbani40

  SSC Enthusiast

  Points: 123

  More actions

  September 4, 2013 at 9:01 am

  #277649

  This Query is not working please help........

  select * from tbl e

  INNER JOIN LKUP L ON e.ID=L.LKUP_ID

  WHERE

  CASE WHEN ISNULL(DATEPART(YY,(SU_DT)),0)=2014 OR ISNULL(DATEPART(mm,(SU_DT)),0)>=11

  THEN (LKUP_VAL_EN LIKE('V2%') OR LKUP_VAL_EN LIKE('V3%'))

  ELSE LKUP_VAL_EN LIKE('V2%')

  END

  AND e.Active=1

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 4, 2013 at 9:16 am

  #1647368

  Sometimes it's easier to work with the expression in an APPLY block, dropping the results down to the WHERE clause like this:

  SELECT e.*,

  x.Datefilter,

  x.LookupFilter

  FROM tbl e

  INNER JOIN LKUP L

  ON e.ID = L.LKUP_ID

  CROSS APPLY (

  SELECT

  Datefilter = CASE

  WHEN YEAR(SU_DT) = 2014

  OR MONTH(SU_DT) >= 11 THEN 1

  ELSE 0 END,

  LookupFilter = CASE

  WHEN LKUP_VAL_EN LIKE 'V2%' THEN 1

  WHEN LKUP_VAL_EN LIKE 'V3%' THEN 2

  ELSE 0 END

  ) x

  WHERE e.Active = 1

  AND (

  (x.Datefilter = 1 AND x.LookupFilter = 1)

  OR (x.Datefilter = 0 AND x.LookupFilter = 2)

  )

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden