Case in where clause

• ptenhet

  Valued Member

  Points: 59

  More actions

  May 14, 2009 at 5:03 pm

  #204801

  I'm on sql server 2000 and trying to select data where (if current month is in the range below, only show records with 1 for Period else show only the records with 2). What's the correct way of doing this?

  Thanks

  pt

  SELECT ID, [month], [year], Period

  FROM dbo.OpMonthlyBonus

  WHERE CASE Period

  WHEN month(getdate()) IN (12, 1, 2, 3, 4, 5) THEN 1

  ELSE 2

  END

• Jeffrey Williams

  SSC Guru

  Points: 90350

  More actions

  May 14, 2009 at 8:17 pm

  #995017

  Not sure of your actual requirements - so, please read the article in my signature if you need additional help.

  Try this:

  SELECT ID, [month], [year], Period

  FROM dbo.OpMonthlyBonus

  WHERE Period = CASE WHEN month(getdate()) IN (12, 1, 2, 3, 4, 5) THEN 1 ELSE 2 END

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Lowell

  SSC Guru

  Points: 323546

  More actions

  May 14, 2009 at 8:36 pm

  #995020

  this was about the only thing that made sense to me;

  SELECT

  ID,

  [month],

  [year],

  CASE

  WHEN month(getdate()) IN (12, 1, 2, 3, 4, 5) THEN 1

  ELSE 2

  END AS Period

  FROM dbo.OpMonthlyBonus

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!