I have event data stored in a table with each one having a start and end date. I need to run a select query where an event for a named day is returned based on whether the duration is in a selected date range.

If the date range is 2022-04-26 to 2022-04-28 and the named day is Tuesday the query will return only event 1 and 2. If the date range is the same and the named date is Wednesday the query will return event 1, event 2 and event 3

CodePudding user response：

I think you can try to use the CASE statement

CodePudding user response：

By converting the start , end date and parameter to daynumber nb check your start of week day...

DROP TABLE IF EXISTS T;
CREATE TABLE t
(event_text varchar(10), start_date date,   end_date date);
insert into t values
('event 1', '2022-04-25'    ,'2022-04-29'),
('event 2', '2022-04-26'    ,'2022-04-28'),
('event 3', '2022-04-27'    ,'2022-04-29');

set @day = 'Wednesday';
select t.*,
         dayofweek(start_date),dayname(start_date), 
         dayofweek(end_date), dayname(end_date),
         d.reqdday
from t
cross join (select 
                case when @day = 'sunday' then 1 
                when @day = 'Monday'  then 2 
                when @day = 'Tuesday' then 3 
                when @day = 'Wednesday' then 4 
                     when @day = 'Thursday' then 5
                when @day = 'Friday' then 6
                when @day = 'Saturday' then 7 
                     end  as reqdday) d
where d.reqdday between  dayofweek(start_date) and dayofweek(end_date)

     ------------ ------------ ------------ ----------------------- --------------------- --------------------- ------------------- --------- 
| event_text | start_date | end_date   | dayofweek(start_date) | dayname(start_date) | dayofweek(end_date) | dayname(end_date) | reqdday |
 ------------ ------------ ------------ ----------------------- --------------------- --------------------- ------------------- --------- 
| event 1    | 2022-04-25 | 2022-04-29 |                     2 | Monday              |                   6 | Friday            |       4 |
| event 2    | 2022-04-26 | 2022-04-28 |                     3 | Tuesday             |                   5 | Thursday          |       4 |
| event 3    | 2022-04-27 | 2022-04-29 |                     4 | Wednesday           |                   6 | Friday            |       4 |
 ------------ ------------ ------------ ----------------------- --------------------- --------------------- ------------------- --------- 
3 rows in set (0.002 sec)