MySQL Query to display different result from multiple condition

Question

I'm searching for a specific SQL Query to automatically set "Priority" cell value if the value of the "Days Left" is :

7 days or less = High
14 days or less = Medium
30 days or less = Low
31 days or more = Very Low

this is my table looks like

Tasks    |  Due Date      |  Days Left | 
---------+----------------+------------+
AAAAA    |  20/12/2019    |      3     |
BBBBB    |  25/12/2019    |      8     |
CCCCC    |  01/01/2020    |     15     |
DDDDD    |  17/01/2020    |     31     |

result i want

Tasks    |  Due Date      |  Days Left |  Priority  | 
---------+----------------+------------+------------+
AAAAA    |  20/12/2019    |      3     |     High   |
BBBBB    |  25/12/2019    |      8     |    Medium  |
CCCCC    |  01/01/2020    |     15     |     Low    |
DDDDD    |  17/01/2020    |     31     |   Very Low |

current mysql query

SELECT
tasks,
due_date,
DATEDIFF(due_date, CURDATE()) AS days_left,
____________________________________ AS priority

You need to do it with case when ... then w3schools

pensum
– pensum

2019-12-17 02:21:57 +00:00
Commented Dec 17, 2019 at 2:21 — pensum
– pensum, Commented Dec 17, 2019 at 2:21

Tim Biegeleisen · Accepted Answer · 2019-12-24 02:20:22Z

3

Use a CASE expression:

SELECT
    tasks,
    due_date,
    DATEDIFF(due_date, CURDATE()) AS days_left,
    CASE WHEN DATEDIFF(due_date, CURDATE()) <= 7  THEN 'High'
         WHEN DATEDIFF(due_date, CURDATE()) <= 14 THEN 'Medium'
         WHEN DATEDIFF(due_date, CURDATE()) <= 30 THEN 'Low'
         ELSE 'Very Low' END AS Priority
FROM yourTable;

Note that, as written, the above CASE expression does not need to check both sides of the inequality range. Rather, we capture higher priorities first, and then let the remaining cases flow to the next step in the CASE expression.

edited Dec 24, 2019 at 2:20

answered Dec 17, 2019 at 2:24

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gnometa Over a year ago

i use datediff function to calculate days_left, but looks like it doesnt work with CASE expression, is there any way i can automatically calculate days_left and use CASE expression from that result?

Tim Biegeleisen Over a year ago

@Gnometa Please check my edited answer. I misread your question initially (I think).

Collectives™ on Stack Overflow

MySQL Query to display different result from multiple condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related