Mysql Question posted in
The official MySQL documentation can be found here.

Find All Sunday dates between two different input fields Mysqli

I have a table that contains: Id, Name, Startdate, Enddate. I would like to Find All Sunday dates between Startdate and Enddate.

Table Mysqli

ID Name Startdate enddate
1 robert 01-01-2024 31-01-2024
2 ann 13-01-2024 20-01-2024
3 ken 20-01-2024 25-01-2024
4 marco 20-01-2024 30-01-2024

Output Result Expected to take all sundays of the persons in the date range.

Name Sundays
robert 07-01-2024
robert 14-01-2024
robert 21-01-2024
robert 28-01-2024
ann 14-01-2024
ken 21-01-2024
marco 21-01-2024
marco 28-01-2024
Tags:

2

Answers


  1. 0

    Assuming you want one row per original row and a comma separated list of dates, this would be:

    with recursive date_range as (
   select id, name, startdate, enddate,
     case when startdate <= enddate then startdate end date_in_range
   from mystery_table_name
   
   union all
   
   select id,name,startdate,enddate,date_in_range + interval 1 day
   from date_range
   where date_in_range < enddate
)
select id, name, startdate, enddate,
  group_concat(case when dayofweek(date_in_range)=1 then date_in_range end order by date_in_range) sundays
from date_range
group by id, name, startdate, enddate
order by id, name, startdate, enddate

    fiddle

    Login or Signup to reply.
  2. 0 
    WITH recursive dates AS (
  SELECT MIN(Startdate) as d FROM mytable
  UNION ALL
  SELECT DATE_ADD(d,INTERVAL 1 DAY) FROM dates WHERE d <= (SELECT MAX(enddate) FROM mytable)
)
SELECT m.Name, d.d
FROM dates d
JOIN mytable m ON d.d BETWEEN m.Startdate and m.enddate
WHERE DAYOFWEEK(d) = 1
ORDER BY d.d;
    • The CTE is creating a temporary result containing all datum from the lowest startdate until the last enddate.
    • Then an JOIN is done against the input
    • using a WHERE to filter only Sundays

    output:

    Name d
    robert 2024-01-07
    robert 2024-01-14
    ann 2024-01-14
    robert 2024-01-21
    ken 2024-01-21
    marco 2024-01-21
    robert 2024-01-28
    marco 2024-01-28

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search