SQL Server SQL CASE statement in the WHERE clause

hi3rlvi2 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(130)

I have a report for a user and the dataset has 3 possible dates they can evaluate. (although this can grow to 5 that we have in our system).
For each of those three dates they can pick a start date and end date for a range.

What i want to do is say if the user picks date1 then evaluate the query as date1 between start date and end date. if the user picks date2 evaluate date2 between start date and end date

@review_dt = value they pick - start_dt, expiration_dt, etc. 

select * from <TABLE>
where fyear = @fyear 
and CASE    WHEN @review_dt = 'start_dt' THEN start_dt between @date_start and @date_end
            WHEN @review_dt = 'expiration_dt' THEN expiration_dt between @date_start and @date_end
            WHEN @review_dt = 'perf_dt' THEN perf_dt between @date_start and @date_end

If this is not something I can do like this - what is a good, clean simple way to do it without a series of nested if statements.

sql-server

来源：https://stackoverflow.com/questions/76622456/sql-case-statement-in-the-where-clause

1条答案

按热度按时间

q3aa05251#

A case expression produces a scalar value, while you want to evaluate conditions. You can use boolean logic instead:

select * 
from mytable
where 
    fyear = @fyear 
    and (
           (@review_dt = 'start_dt'      and start_dt      between @date_start and @date_end)
        or (@review_dt = 'expiration_dt' and expiration_dt between @date_start and @date_end)
        or (@review_dt = 'perf_dt'       and perf_dt       between @date_start and @date_end)
     )
option (recompile)

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL CASE statement in the WHERE clause

1条答案

相关问题

热门标签

最新问答