CODEWITHSUNDEEP
sql-servert-sqlssrs-2008reportbuilder3.0case-when
Newbie1331
Newbie1331

Reputation: 81

SSRS 2008 Report Builder 3.0: Using CASE and WHEN Function

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:

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 ) >= @StartHour
AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) < @EndHour

Upvotes: 1

Views: 1903

Answers (1)

Pratik
Pratik

Reputation: 1512

Missed to answer the last day, but still if you haven't solved the issue. I would suggest to give a try using union for your second shift data. As the day change in selecting between time range

/*******************************************************
   --Commented 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 
  *******************************************/

 If(@TimeRange=1)
 BEGIN
 SELECT
    Testerline1_CycleTimes.Station,
    Testerline1_CycleTimes.StationEntry
 WHERE
    sterline1_CycleTimes.Station LIKE @Station
    AND Testerline_CycleTimes.StationEntry Between @Start And @End
    AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) >= 6
    AND DATEPART( hh , Testerline1_CycleTimes.StationEntry ) < 16
END

ELSE IF(@TimeRange=2)
BEGIN
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 ) >= 16
UNION ALL
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 ) < 3
END

 ELSE
 BEGIN
 SELECT
      Testerline1_CycleTimes.Station,
      Testerline1_CycleTimes.StationEntry
 WHERE
     Testerline1_CycleTimes.Station LIKE @Station
     AND Testerline1_CycleTimes.StationEntry Between @Start And @End
 END

Upvotes: 1

Related Questions