Dynamic Case Expression Based on Dates

Question

I have a SQL query that has multiple conditions that will change the WHERE clause. I have three case expressions that dynamically update this WHERE clause. The last two case expressions are working well. However, the first one is more dynamic than the last two and I am having issues with it.

Here is the portion of the table that is applicable to this exercise (Image of Table)

I need it to work as follows: When my variable @selection = 1, I need it to sort my return based on two dates being passed in. When my variable @selection = {any other int}, I need it to return all dates.

Query with Description
...
WHERE
{Start Date} = (case when @selection = 1 then {return all values where Start Date is between two dates that are passed in} else {return all Start Dates})
...

Entire Query

DECLARE @selection integer ;
DECLARE @items integer ;
DECLARE @washTypes integer ;

SET @selection = {Root Container.Selection Group.Selection Checkbox.controlValue} ;
SET @items = {Root Container.Items Group.Items Checkbox.controlValue} ;
SET @washTypes = {Root Container.Types of Wash Group.Wash Types Checkbox.controlValue} ;

SELECT

RAW_CIP_records_ndx as 'Index', 
start as 'Start', 
stop as 'End', 
total_duration as 'Total Duration', 
item as 'Item', 
wash_type as 'Type of Wash', 
operator as 'CIP Operator', 
program_complete as 'Program Fully Completed?'

FROM RAW_CIP_records

WHERE
start = (case when @selection = 1 then (BETWEEN '{Root Container.Start Date.formattedDate}' and '{Root Container.End Date.formattedDate}') else start end)
and 
item = (case when @items = 0 then 'Receiving Bay 1' when @items = 1 then 'Receiving Bay 2' when @items = 2 then 'Receiving Bay 3' else item end)
and
wash_type = (case when @washTypes = 0 then 'Regular' when @washTypes = 1 then 'Sanitize' when @washTypes = 2 then 'Acid' else wash_type end)

I can get a simple WHERE clause to work with these two dates and the BETWEEN function. However, I can't figure out how to pass all of this into a CASE expression.

I'm not sure I understand the question really but is this what you need ? `WHERE (case when @selection = 1 then (start BETWEEN '{Root Container.Start Date.formattedDate}' and '{Root Container.End Date.formattedDate}') else 1=1 end)` — Modar Na, Mar 25 '21 at 22:22
I've tried this: WHERE (case when @selection = 1 then (start BETWEEN '{Root Container.Start Date.formattedDate}' and '{Root Container.End Date.formattedDate}') else start = start end) but it gives me an error saying 'Incorrect syntax near the keywork 'BETWEEN'. — AJS, Mar 25 '21 at 22:37
try declaring `{Root Container.Start Date.formattedDate}` in variables as you did with `@selection` and use them in your query instead — Modar Na, Mar 25 '21 at 22:59
You could do it one query `WHERE (@selection = 1 AND (start BETWEEN @startDate and @endDate) or @selection <> 1)`. But I would suggest you are better of with two separate queries `IF @selection = 1 ... ELSE ...` I do sincerely hope you are passing through those dates as proper parameters, rather than embedding the values in the query text via concatenation/interpolation/`string.Format` — Charlieface, Mar 26 '21 at 02:40
[This](https://stackoverflow.com/a/10260297/92546) answer shows how to use `case` in an `on` (or `where`) clause. Now read the Sommarskog article that David Browne provided a link to. — HABO, Mar 26 '21 at 03:56
@Charlieface Your query also worked. I have changed out the string format dates for variables as well. Thanks for the information. — AJS, Mar 26 '21 at 14:20
Your *variables* also use `string.Format` in the `SET`, do you mean *parameters*? — Charlieface, Mar 26 '21 at 15:24
Yes, good note. I've changed them to be parameters as they others are. — AJS, Mar 26 '21 at 17:39

score 1 · Accepted Answer · answered Mar 26 '21 at 15:26

You could do it a WHERE condition in one query

WHERE (
     @selection = 1 AND (start BETWEEN @startDate and @endDate)
  OR @selection <> 1
)

But I would suggest you are better off with two separate queries as it will be more likely to use an index.

IF @selection = 1
    SELECT ...
ELSE
    SELECT ...

score 0 · Answer 2 · answered Mar 26 '21 at 03:02

Maybe something like this:

WHERE
1 = (case when @selection = 1 then 
            case when start BETWEEN '{Root Container.Start Date.formattedDate}' and '{Root Container.End Date.formattedDate}' then 1 else 0 end
        else 1
     end)
and
item = ...

Warning: this will most likely not make use of any indexes, so only use if performance is acceptable in your case.

Dynamic Case Expression Based on Dates

2 Answers2