1
votes

I'm trying to build a SSRS report with multiple parameters, and when I add a standard START_DATE and END_DATE parameter I get the expected results from my SQL query line: WHERE SOME_FIELD BETWEEN (:P_START_DATE) and (:P_END_DATE)

I would like to do something like the following scenario:

Setup:

  1. Date Field 1 & 2: WHERE NEXT_SHIP_DUE_DATE BETWEEN (:P_NEXT_SHIP_START_DATE) AND (:P_NEXT_SHIP_END_DATE)
  2. Date Field 3 & 4: AND PRIMARY_IMAGE_DATE BETWEEN (:P_PID_START_DATE) AND (:P_PID_END_DATE)

All date parameters in SSRS report allow NULL values and are initially set to NULL.

User should be able to:

  1. Get all records when no date field parameters are requested
  2. Get only records based off the date fields that user selects This may be 1 or more.

Scenarios:

  1. Example 1: User selects no date fields - returns no data
  2. Example 2: User selects date fields 1 & 2 only - returns no data
  3. Example 3: User selects all date fields same date range - RETURNS CORRECT DATA
  4. Example 4: User selects all date fields different date ranges - SSRS error: date not valid for month specified

I've tried several different pieces of SQL but no luck so far. Any help would be appreciated!

1

1 Answers

1
votes

You need to add some kind of null check, be careful of brackets.

WHERE (NEXT_SHIP_DUE_DATE BETWEEN (:P_NEXT_SHIP_START_DATE) AND (:P_NEXT_SHIP_END_DATE) OR ((:P_NEXT_SHIP_END_DATE) IS NULL AND (:P_NEXT_SHIP_START_DATE) IS NULL))

AND (PRIMARY_IMAGE_DATE BETWEEN (:P_PID_START_DATE) AND (:P_PID_END_DATE) OR ((:P_PID_END_DATE) IS NULL AND (:P_PID_START_DATE) IS NULL))