Using CASE in WHERE clause

using case in where clause oracle
sql case statement in where clause multiple values
case inside where clause sql
sql case statement in where clause with parameters
case in where clause mysql
sql case when multiple values
tsql case in where clause
multiple case when sql

I have 2 tables that needs to be joined based on couple of parameters. One of the parameters is year. One table contains the current year but another year doesn't contain current year, so it must use the latest year and matched with other parameters.

Example

Product

-------------------------------------------------------------------------------
| product_id | category_id | sub_category_id | product_year | amount |
-------------------------------------------------------------------------------
| 504        | I           | U               | 2020         | 400    |
| 510        | I           | U               | 2019         | 100    |
| 528        | I           | U               | 2019         | 150    |
| 540        | I           | U               | 2018         | 1000   |

Discount

-----------------------------------------------------------------------------
| discount_year | category_id | sub_category_id | discount |
-----------------------------------------------------------------------------
| 2018          | I           | U               | 0.15     |
| 2017          | I           | U               | 0.35     |
| 2016          | I           | U               | 0.50     |

Output

-----------------------------------------------------------------------------
| product_id | category_id | sub_category_id | product_year | discount_year |
-----------------------------------------------------------------------------
| 504        | I           | U               | 2020         | 2018          |
| 510        | I           | U               | 2019         | 2018          |
| 528        | I           | U               | 2019         | 2018          |
| 540        | I           | U               | 2018         | 2017          |

The discount is always gotten from one year behind but if those rates aren't available, then it would keep going back a year until available.

I have tried the following:

SELECT 
    product_year, a.product_id, a.category_id, a.sub_category_id, 
    discount_year, amount, discount
FROM
    Product a
INNER JOIN 
    Discount b ON a.category_id = b.category_id 
               AND a.sub_category_id  = b.sub_category_id
               AND product_ year = CASE
                                      WHEN discount_year + 1 = product_year 
                                         THEN discount_year + 1
                                      WHEN discount_year + 2 = product_year 
                                         THEN discount_year + 2
                                      WHEN discount_year + 3 = product_year 
                                         THEN discount_year + 3
                                   END
 WHERE 
     product = 540

This return the following output:

--------------------------------------------------------------------------------------------------
| product_year | product_id | category_id | sub_category_id | discount_year | amount | discount |
--------------------------------------------------------------------------------------------------
| 2016         | 540        | I           | U               | 2017          | 1000   | 0.50     |
| 2017         | 540        | I           | U               | 2017          | 1000   | 0.35     |

Any help will be appreciated.

You can use OUTER APPLY and a subquery. In the subquery select the row with the maximum discount_year, that is less the product_year using TOP and ORDER BY.

SELECT p.product_year,
       p.product_id,
       p.category_id,
       p.sub_category_id,
       d.discount_year,
       p.amount,
       d.discount
       FROM product p
            OUTER APPLY (SELECT TOP 1
                                *
                                FROM discount d
                                WHERE d.category_id = p.category_id
                                      AND d.sub_category_id = p.sub_category_id
                                      AND d.discount_year < p.product_year
                                ORDER BY d.discount_year DESC) d;

Using CASE statements in WHERE clause, Your where clause should look something like this if you want to use a case statement inside of it: WHERE (CASE WHEN (dateadd(dd,  Question: How to Write Case Statement in WHERE Clause? Answer: This is a very popular question. This question usually comes up in the context of writing search condition where the user is not sure if there will be condition or not. First of all, as Kalen Dealaney mentioned (Thank you!) on the twitter Case is not a statement it is an expression.

instead of a CASE expression you can use a sub-query to select the TOP 1 related discount_year that is less than your product_year, ORDER BY discount_year ASC.

How to Write Case Statement in WHERE Clause?, You can clearly see how with the help of CASE expressions, we can create a situation where without using dynamic SQL we can selectively apply  Using a Case Statement in a SQL Where Clause It is common knowledge that you can apply a Case statement in the SELECT or ORDER BY portion of a SQL statement. What isn’t well known is that you can use it effectively in a WHERE clause.

Create a product to discount mapping using a CTE first. This contains the discount year pulled from discount table for every product year in the product table and corresponding product_id. Following this, you can easily join with relevant tables to get results and eliminate any nulls as needed

Simplified query.

 ;WITH disc_prod_mapper 
    AS
    (
    SELECT  product_id, product_year,(SELECT MAX(discount_year) FROM  #Discount b WHERE discount_year < product_year AND a.category_id  = b.category_id  AND a.sub_category_id   = b.sub_category_id ) AS discount_year
     FROM Product a  
    )
    SELECT a.product_year, c.discount_year, a.amount, c.discount
    FROM Product a
      LEFT JOIN disc_prod_mapper b   ON a.product_id  = b.product_id   
      LEFT JOIN Discount c ON b.discount_year = c.discount_year

CASE statement in SQL, We can use a Case statement in select queries along with Where, Order By and Group By clause. It can be used in Insert statement as well. The CASE in T-SQL is an expression that can return one of several atomic values - it cannot however return or handle code blocks, nor can it return half a WHERE clause. – marc_s Jan 16 '14 at 21:26 8 5 Reverend Bubbles Reverend Bubbles

SQL CASE, This SQL tutorial for data analysis covers using if/then logic with the SQL The CASE statement always goes in the SELECT clause; CASE must include the  I don't know what you are thinking with the CASE statement - case is a statement that returns an expression like 'Hello world'. Case cannot return "sql" to be processed. You would just use a where clause for this:

How to use a CASE expression in the WHERE clause?, You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might  CASE Statement: The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE. Example 13-2 Using a CASE Statement. DECLARE

SQL Server: Use CASE in WHERE Clause, In this article I am going to show how we can use CASE in WHERE clause. Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.