CODEWITHSUNDEEP
sqlsql-server
Touhid K.
Touhid K.

Reputation: 341

SQL Server - Query with tricky where clause

I have a report where user can select one filter value amongst these three and can pass a date range ('From Date' and 'To Date') before clicking the submit button:

  1. Events within Date Range
  2. New Guests within Date Range
  3. New Guests Beyond Date Range

I want to fire a select query on myTable for the selected filter value.

If user selects first value (1), the query should give all the records where dtEventDate is between the date range that is entered.

If user selects second value (2), the query should give all the records where dtGuestCreatedDate is between the date range that is entered.

Similarly, If user selects third value (3), the query should give all the records where dtGuestCreatedDate is greater than 'To Date'.

My query would be like,

Select * from myTable
where
if '#filterValue#'='Events within Date Range'
then myTable.dtEventDate between '' and ''
else if '#filterValue#'='New Guests withing Date Range'
then myTable.dtGuestCreatedDate between '' and ''
else myTable.dtGuestCreatedDate > ''

I dont know how do I write this condition.

I have this so far that would give me events within date range:

select * from myTable
where myTable.dtEventDate between
case when '#filterValue#'='Events Within Date Range' then '10/01/2016' end
and '11/30/2016'

But I need to check all the three filter values in one where clause.

Can anybody help please?

Upvotes: 0

Views: 82

Answers (2)

Henning Koehler
Henning Koehler

Reputation: 2637

select * from myTable
where case @filtervalue
    when 'Events within daterange' then myTable.dtEventDate between ...
    when 'New Guests within daterange' then ...
    ...
    end

Also, I would strongly recommend that you use parameterized queries to avoid SQL injection attacks. Assuming your environment permits that.

Upvotes: 0

Jakub Rusilko
Jakub Rusilko

Reputation: 867

You could do something like this based on the example that you gave:

Select * from myTable
where
    ('#filterValue#'='Events within Date Range' AND myTable.dtEventDate between '' and '')
    OR
    ('#filterValue#'='New Guests withing Date Range' AND myTable.dtGuestCreatedDate between '' and '')
    OR
    ('#filterValue#'='New Guests Beyond Date Range' AND myTable.dtGuestCreatedDate > '')

Upvotes: 1

Related Questions