'SQL for Ordering event data by comparing to two dates

I want to sort the data by the current month to compare it with two dates.

I also need to order data by the current month. This means, that if the event start date or event end date is in the current month, those events need to show at the top.

For example - in my events table.

Event Name event_start_date event_end_date
Event 1 2022-06-09 2022-06-11
Event 2 2022-07-08 2022-07-30
Event 3 2022-05-29 2022-06-15
Event 4 2022-06-15 2022-06-16

If the current month is June (06) then I want to get data by this order--

  1. Event 3
  2. Event 1
  3. Event 4
  4. Event 2

at Event 3 start date is past month, but the end date is in the current month. So I want it in the first position. I have tried this query

IF(MONTH(event_start_date) < MONTH(NOW()), MONTH(event_start_date) + 12, MONTH(event_start_date)), DAY(event_start_date)

So in my last query, I have compared only event_start_date, but which events start from the past month and end at the current month, I also need them at the top.

So how to solve it? Thanks

mysqlsql


Solution 1:[1]

Seems like the following query is working for my case.

ORDER BY IF(
            
        MONTH(IF(MONTH(event_start_date)=MONTH(NOW()), event_start_date, IF(MONTH(event_end_date)=MONTH(NOW()), event_end_date, event_start_date))) < MONTH(NOW()), 
        MONTH(IF(MONTH(event_start_date)=MONTH(NOW()), event_start_date, IF(MONTH(event_end_date)=MONTH(NOW()), event_end_date, event_start_date))) + 12,
        MONTH(IF(MONTH(event_start_date)=MONTH(NOW()), event_start_date, IF(MONTH(event_end_date)=MONTH(NOW()), event_end_date, event_start_date))) 
    ), 
    DAY(IF(MONTH(event_start_date)=MONTH(NOW()), event_start_date, IF(MONTH(event_end_date)=MONTH(NOW()), event_end_date, event_start_date)))

Thanks.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Mohammad Sheam

Related Questions