SQL repeat events with different start date and end date

Question

I have tables calendar_events: id, name, start_date, end_date calendar_event_repetitions: calendar_event_id, interval, ends_at

I have query like:

WITH RECURSIVE date_range AS (
            SELECT DATE('2024-02-26 00:00:00') AS date_value
            UNION ALL
            SELECT DATE_ADD(date_value, INTERVAL 1 DAY)
            FROM date_range
            WHERE date_value < '2024-02-26 23:59:59' -- Adjust the end date as per the user input
        )
        SELECT DISTINCT ce.*
        FROM calendar_events ce
        LEFT JOIN calendar_event_repetitions cer ON ce.id = cer.calendar_event_id
        WHERE ce.user_id = '837717ff-4746-4451-9986-c5529d671c52' AND (
            (ce.start_date <= '2024-02-26 23:59:59' AND ce.end_date >= '2024-02-26 00:00:00')
            OR (
                (cer.ends_at <= '2024-02-26 23:59:59' OR cer.ends_at IS NULL)
                AND (
                    SELECT COUNT(*)
                    FROM date_range
                    WHERE (
                        (
                            cer.interval IS NOT NULL
                            AND cer.interval_type = 'd'
                            AND TIMESTAMPDIFF(DAY, ce.start_date, date_range.date_value) % cer.interval = 0
                            AND cer.weekly_days IS NULL
                        )
                    )
                ) > 0
            )
        );

It works only for events where start_date and end_date the same. But if I have, for example, start_date 2024-02-19, end_date 2024-02-22 and I want repeat that event every 5 days, it doesn't work. How can I include that condition where it checks repeating range?

For example it should return this even for dates 2024-02-26, 2024-02-27. But for date 2024-02-28 it shouldn't.

Can anyone help me with this case?

Answer 1

One-table DEMO:

CREATE TABLE test (
  id INT,
  startdate DATE,
  enddate DATE,
  period INT
  );
INSERT INTO test VALUES
(1, '2024-01-01', '2024-01-10', 4), -- 1, 5, 9
(2, '2024-01-01', '2024-01-10', 3), -- 1, 4, 7, 10
(3, '2024-01-01', '2024-01-05', 7), -- 1
(4, '2024-01-10', '2024-01-01', 4); -- none
SELECT * FROM test;

id	startdate	enddate	period
1	2024-01-01	2024-01-10	4
2	2024-01-01	2024-01-10	3
3	2024-01-01	2024-01-05	7
4	2024-01-10	2024-01-01	4

WITH RECURSIVE cte AS (
  SELECT id, startdate, enddate, period, startdate AS onedate
  FROM test
  WHERE startdate <= enddate  -- check that startdate is valid
  UNION ALL
  SELECT id, startdate, enddate, period, onedate + INTERVAL period DAY
  FROM cte
  WHERE onedate + INTERVAL period DAY <= enddate -- check that next date is in range
)
SELECT * FROM cte ORDER BY id, onedate;

id	startdate	enddate	period	onedate
1	2024-01-01	2024-01-10	4	2024-01-01
1	2024-01-01	2024-01-10	4	2024-01-05
1	2024-01-01	2024-01-10	4	2024-01-09
2	2024-01-01	2024-01-10	3	2024-01-01
2	2024-01-01	2024-01-10	3	2024-01-04
2	2024-01-01	2024-01-10	3	2024-01-07
2	2024-01-01	2024-01-10	3	2024-01-10
3	2024-01-01	2024-01-05	7	2024-01-01

fiddle

If a table have CHECK constraint which does not allow startdate to be above enddate then WHERE from the anchor subquery must be removed.