title: SSRS 2008 Report Builder 3.0: Using CASE and WHEN Function
body: <p>I have my program set up to allow the user to select a station, then pick between 2 dates, then they pick what shift they want. They can pick Both, First, or Second. First is 6:00AM - 4:00PM and Second is 4:00PM - 3:00AM. I have 4 parameters in total. I think my program returns no values for second shift because not all values between 16 and 3 are greater then start and less then end. For example 6:00PM is not less than end hour of 3 so it is not included in output. How do I achieve this? Here is my code:</p>
  
  <pre><code>DECLARE @StartHour INT
  DECLARE @EndHour INT
  SET @StartHour = CASE @TimeRange 
  
  WHEN 0 THEN 0 --Start of Overall--
  WHEN 1 THEN 6 --1st Shif Start--
  WHEN 2 THEN 16 --2nd Shift Start--
  END
  
  SET @EndHour = CASE @TimeRange   
  WHEN 0 THEN 24 --End of Overall--
  WHEN 1 THEN 16 --End of First Shift--
  WHEN 2 THEN 3 --End of Second Shift--
  END
  
  SELECT
  Testerline1_CycleTimes.Station,
  Testerline1_CycleTimes.StationEntry
  
  WHERE
  Testerline1_CycleTimes.Station LIKE @Station
  AND Testerline1_CycleTimes.StationEntry Between @Start And @End
  AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) &gt;= @StartHour
  AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) &lt; @EndHour
  </code></pre>