SQL SSRS | WHERE using CASE WHEN NULL, ELSE BETWEEN parameters

Question

Hello: I'm trying to search exclusively on a Date field in an SSRS report where:

• Date BETWEEN @StartDate and @EndDate
• Date IS NULL
• Date IS NULL OR BETWEEN @StartDate and @EndDate

I have the OR statement working:

WHERE ((Date BETWEEN @StartDate AND @EndDate) OR (Date IS NULL))

The issues I have are:

Can I CASE ELSE to start BETWEEN clause?

CASE WHEN @ChkStartDate IS NULL OR @ChkEndDate IS NULL THEN CheckReg.CheckDate IS NULL
    ELSE CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate
    END)

Can I configure the SSRS report so the user can search on all three Date conditions?

1. Date BETWEEN
2. DATE IS NULL
3. DATE BETWEEN OR DATE IS NULL

Thanks ALL

Answer 1

I accomplished the exclusive WHERE searches by adding a Boolean parameter to the report.

WHERE 
(
(newparameter = '1' AND CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate)
OR
(newparameter = '0' AND ((CheckReg.CheckDate BETWEEN @ChkStartDate and @ChkEndDate) OR CheckReg.CheckDate IS NULL))
)