0

I need to change the status column depending on other two columns which are dates.

| id | start      | end        | status    |
| 1  | 2020-04-23 | 2020-04-28 | inprogress| -->current date is > than start date and < than end date
| 2  | 2020-02-20 | 2020-02-25 | ended     | -->current date is > than end date
| 3  | 2020-05-15 | 2020-05-20 | upcoming  | -->current date is < than start date

So as you can see we can have 3 types of STATUS to be set, INPROGRESS, ENDED, UPCOMING Is there a way to set these statuses based on the START and END columns? If this cannot be done, can you suggest a way to implement such thing in the application?

Vasil Emilov
  • 47
  • 8
  • 1
    You are pretty much there - use a case statement to test and set the status. – P.Salmon Apr 23 '20 at 13:07
  • @P.Salmon well... I should've known about the CASE statement xD. How do you suggest I should schedule it so it does it automatically when the status should be changed. How to determine intervals at which the event should be executed? I've never created a scheduled event before – Vasil Emilov Apr 23 '20 at 13:10
  • 1
    ' so it does it automatically when the status should be changed.' - set up an event and run it daily see https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html – P.Salmon Apr 23 '20 at 13:12

1 Answers1

0

As P.Salmon suggested in the comment section. I used CASE statement along with scheduling it to run daily to achieve the wanted result

Vasil Emilov
  • 47
  • 8