Case When: Using > Dates and < Dates With Multiple Conditions

Question

I have a CASE WHEN statement with three layers, each defining a specific Limit criteria that meets a certain category for A, B, or C. A and B are heavily dependant on > or < dates.

The criteria are as follows:

When Date is less than 01-OCT-2019 AND Limit is greater than 10,000 Then 'A'

When Date is greater than 01-OCT-2019 AND Limit is less than 90,000 And Plan = CP Then 'B'

When Date is greater than 01-OCT-2019 AND Limit is less than 60,000 And Plan = CO Then 'B'

ELSE 'C'

This has been written out as

 CASE 
    WHEN
          TO_CHAR(DATE,'dd-mm-yyyy') < '01-10-2019'
          AND Limit > 10000
          THEN 'A'         
    WHEN 
          TO_CHAR(DATE,'dd-mm-yyyy') > '01-10-2019'
          AND Limit < 90000
          AND Plan = 'CP'
          THEN 'B' 
    WHEN 
          TO_CHAR(DATE,'dd-mm-yyyy') > '01-10-2019'
          AND Limit < 60000
          AND Plan = 'CO'
          THEN 'B'
          ELSE 'C' 
    END AS Category

Although the query runs, the categorized results are not accurate according to the date conditions. For instance:

For Category B, I should only be retrieving dates after 01-10-2019 (dd-mm-yyyy), but I am getting dates that stretch back to 2018.

+----+------------+---------+------+----------+---+
| ID |    Date    |  Limit  | Plan | Category |   |  
+----+------------+---------+------+----------+---+
|  1 | 20-11-2018 |  67000  |  CP  |    B     | X |  
|  2 | 08-08-2019 |  32000  |  CO  |    C     | ✓ |  
|  3 | 05-03-2019 |  12000  |  CO  |    A     | ✓ |  
|  4 | 18-10-2019 |  70000  |  CP  |    B     | ✓ |
|  5 | 16-04-2019 |  52000  |  CO  |    B     | X |
|  6 | 23-07-2018 |  17000  |  CP  |    A     | ✓ |     
+----+------------+---------+------+----------+---+

The database shows the Date structured as '01-OCT-2019', however, I want the output to show as '01-10-2019'. In this case, I have included the To_Char function.

Any suggestions for fixing this date issue?

Answer 1

You need to simply check dates, instead of converting them to strings, given that the date order is different from the lexicographic order applied to the string that represents the date.

Your code could be:

CASE 
    WHEN
          DATE < date '2019-10-01'
          AND Limit > 10000
          THEN 'A'         
    WHEN 
          DATE > date '2019-10-01'
          AND Limit < 90000
          AND Plan = 'CP'
          THEN 'B' 
    WHEN 
          DATE > date '2019-10-01'
          AND Limit < 60000
          AND Plan = 'CO'
          THEN 'B'
          ELSE 'C' 
    END AS Category

Notice that I've kept your DATE column, even if it's a reseved word; the date '2019-10-01' part is the ANSI way to express a date in Oracle.

In select list, you can use to_char the way you need to get a formatted output.