Use Datediff only if other column has specific value

What is the query for showing days_left in number of days but only if the status is ‘ongoing’, for the status that ‘done’ it just need to display ‘done’ value

Current MySQL Query

SELECT
id,
due_date,
process_1,
process_2,
CASE WHEN FLOOR((IF(process_1= 'Done', 1, 0) + IF(process_2= 'Done', 1, 0))/2 * 100) = 100 THEN 'Done'
ELSE 'Ongoing' END AS status,
DATEDIFF(due_date, CURDATE())) AS days_left
FROM table1

Below is my result

+---------------------------------------------------------------+
| id | due_date   | process_1 | process_2 | status  | days_left |
+ ----------------------------+-----------+---------+-----------+
|  1 | 10/01/2020 | ongoing   | ongoing   | ongoing |    3      |
|  2 | 10/01/2020 | done      | done      | done    |    3      |
+---------------------------------------------------------------+

Result i want

+---------------------------------------------------------------+
| id | due_date   | process_1 | process_2 | status  | days_left |
+ ----------------------------+-----------+---------+-----------+
|  1 | 10/01/2020 | ongoing   | ongoing   | ongoing |    3      |
|  2 | 10/01/2020 | done      | done      | done    |   done    |
+---------------------------------------------------------------+

Answer

In your query, you have equations for calculating your status. You can use the same equation again to specify your days_left, somewhat like:

SELECT
id,   
due_date,
process_1,
process_2,
CASE WHEN 
  FLOOR((IF(process_1= 'Done', 1, 0) + IF(process_2= 'Done', 1, 0))/2 * 100) = 100 
  THEN 'Done'
  ELSE 'Ongoing' END AS status
CASE WHEN 
  FLOOR((IF(process_1= 'Done', 1, 0) + IF(process_2= 'Done', 1, 0))/2 * 100) = 100 
  THEN 'Done' 
  ELSE DATEDIFF(due_date, CURDATE())) END AS days_left
FROM table1

Advertisement

Answer