I am very much new to the mysql and sincere apologies as struggling to find the right approach for the question.
I have a table which is containing the events schedule time:
Event_id 1 will be running on every Monday in between 03-04-2022 to 03-12-2022.I need to find the total run count of the each Event_id in between a date range.
You need to find number of day between start and end date. E.g. how many Monday between start and end date for event_id=1 and so on.
In the question start_date and end_date for event_id=4 are not correct as start_date > end_date. Also, there is a spelling mistake in day name for evnet_id=4.
The query used for getting the result, after fixing data issues is -
WITH RECURSIVE
date_gen (gen_dt) AS
(
SELECT min(start_date) from event_tab
union all
SELECT date_add(gen_dt,interval 1 day) from date_gen where gen_dt < (select max(end_date) from event_tab)
)
SELECT
e.event_id,
sum(case when dayname(d.gen_dt) = e.schedule_day then 1 else 0 end ) day_nos_as_run_count
FROM date_gen d, event_tab e
where
cast(d.gen_dt as date) between e.start_date and e.end_date
group by e.event_id
DB fiddle here.
Recursive query example help from here.
Sample output -
| event_id | day_nos_as_run_count |
|---|---|
| 1 | 35 |
| 2 | 23 |
| 3 | 2 |
| 4 | 0 |
Another variant of the query -
WITH RECURSIVE
date_gen (gen_dt) AS
(
SELECT min(start_date) from event_tab
union all
SELECT date_add(gen_dt,interval 1 day) from date_gen where gen_dt < (select max(end_date) from event_tab)
)
SELECT
e.event_id,
count(d.gen_dt)
FROM event_tab e left join date_gen d
on ( cast(d.gen_dt as date) between e.start_date and e.end_date
and e.schedule_day = dayname(d.gen_dt))
group by e.event_id
order by e.event_id
Start by storing dates in date format: 03-04-2022 should be stored as 2022-04-03 (assuming that your format is day-month-year). And store into a column declared to be DATE, not VARCHAR.
Then you have the ability to use the DATEDIFF() function to get differences.
