Recurring events, SQL query

Question

I know that questions about recurring events are common, but I could not find an answer with this specific question about recurring events other than those related to calendar applications. The main difference is that the events in our application. will be visible only in reports or on their own, and not in a calendar format, although in many respects they are very similar, possibly with less luggage associated with calendars.

Similar to the calendar application. events can occur either once or can be repeated, for example. every Thursday or first Monday of every month, until a certain time in the future.

Events are stored in the event table, which contains the start and end dates and the “revaluation type identifier”. If the “return type” is “No,” then the start and end dates will be the same. The event table contains the identifier of a single table that contains the name of the event type, for example. Meeting or Weekly Report

There is another table that contains a list of “revaluation types,” for example. No Repeat, Every Monday, First Monday of the Month, and Last Saturday of the Month.

To simplify the search, another table contains a list of dates from 1960 to 2060, as well as relevant information about each date, for example, whether it is Monday and which appearance on Monday is the month.

This allows you to search for:

 SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt,r.recurring FROM dates d LEFT JOIN recurringtypes r /* if event recurring every week Eg 'Every Monday' */ ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow) /* if event recurring every month Eg 'First Monday, every month' */ OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence) /* if event recurring every last week of month Eg 'Last Monday, every month' */ OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast) LEFT JOIN events e on e.rectypeid = r.rectypeid LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid LEFT JOIN names n ON e.namesid = n.namesid WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01') ORDER BY d.dt; 

This is exactly what is required to search for recurring events yielding output:

 +-----------+---------------+-------------------+-----------+------------+-------------------------------+ | eventid | nameid | lastname | firstname | dt | recurring | +-----------+---------------+-------------------+-----------+------------+-------------------------------+ | 3291788 | 1728449 | smith | zoe | 2012-02-02 | First Thursday, every month | | 3291797 | 1765432 | | | 2012-02-05 | First Sunday, every month | | 3291798 | 1730147 | | | 2012-02-05 | First Sunday, every month | | 3291803 | 1790061 | Carpenter | Richie | 2012-02-06 | Every Monday | | 3291805 | 1790061 | Carpenter | Richie | 2012-02-08 | Second Wednesday, every month | | 3291803 | 1790061 | Carpenter | Richie | 2012-02-13 | Every Monday | | 3291799 | 1790061 | Carpenter | Richie | 2012-02-15 | Third Wednesday, every month | | 3291803 | 1790061 | Carpenter | Richie | 2012-02-20 | Every Monday | 

To avoid recurring events, you can use a simpler query:

 SELECT n.nameid,n.lastname,n.firstname,e.firstdate,e.eventid,'No' as Recurring FROM events e LEFT JOIN names n ON n.names = e.namesid AND e.rectypeid <= 1 AND e.firstdate BETWEEN '2012/02/01' AND '2012/05/01' AND e.eventid IS NOT NULL ORDER BY e.firstdate; 

This gives a result very similar to the first query, but, importantly, the dates are taken from the event table, not the date table.

My question is: how to combine these queries to create a single list containing all events repeating and repeating in date order?

---

These are tables and shortened versions of them, some columns and all indexes have been deleted for brevity :). The table "names" was not included for the same reason.

 CREATE TABLE events ( eventid int(11) NOT NULL AUTO_INCREMENT, eventtypeid int(11) DEFAULT '0', firstdate date DEFAULT '1960-01-01' COMMENT 'First event', lastdate date DEFAULT '1960-01-01' COMMENT 'Last event', rectypeid int(11) DEFAULT '1' ); +---------+-------------+------------+------------+-----------+ | eventid | eventtypeid | firstdate | lastdate | rectypeid | +---------+-------------+------------+------------+-----------+ | 3291803 | 16 | 2012-02-03 | 2012-04-11 | 3 | | 3291797 | 8 | 2012-02-12 | 2012-02-22 | 9 | | 3291798 | 5 | 2012-02-12 | 2012-02-12 | 9 | | 3291788 | 8 | 2012-05-24 | 2015-01-16 | 13 | | 3291805 | 10 | 2012-01-04 | 2012-02-14 | 19 | | 3291799 | 16 | 2012-02-07 | 2012-10-24 | 26 | | 3291804 | 5 | 2012-02-03 | 2012-08-22 | 41 | +---------+-------------+------------+------------+-----------+ CREATE TABLE cmseventtypes ( eventtypeid int(11) NOT NULL AUTO_INCREMENT, eventtype varchar(50) DEFAULT '' COMMENT 'Event type AKA name' ); +-------------+----------------------+ | eventtypeid | eventype | +-------------+----------------------+ | 1 | Follow up meeting | | 2 | Reminder email due | | 3 | Monthly meeting | | 4 | Weekly report | | 5 | Golf practice | +------------------------------------+ CREATE TABLE recurringtypes ( rectypeid int(11) NOT NULL AUTO_INCREMENT, recurring varchar(40) DEFAULT '', day tinyint(4) DEFAULT '0', occurrence tinyint(4) DEFAULT '0', islast tinyint(4) DEFAULT '0' ); +-----------+---------------------------+------+------------+--------+ | rectypeid | recurring | day | occurrence | islast | +-----------+---------------------------+------+------------+--------+ | 1 | No | 0 | 0 | 0 | | 2 | Every Sunday | 1 | 0 | 0 | | 3 | Every Monday | 2 | 0 | 0 | | 4 | Every Tuesday | 3 | 0 | 0 | | 5 | Every Wednesday | 4 | 0 | 0 | | 6 | Every Thursday | 5 | 0 | 0 | | 7 | Every Friday | 6 | 0 | 0 | | 8 | Every Saturday | 7 | 0 | 0 | | 9 | First Sunday, every month | 1 | 1 | 0 | | 10 | First Monday, every month | 2 | 1 | 0 | +-----------+---------------------------+------+------------+--------+ CREATE TABLE dates ( dt date NOT NULL COMMENT 'Date', daycount mediumint(9) NOT NULL DEFAULT '1', year smallint(6) NOT NULL DEFAULT '1970', month tinyint(4) NOT NULL DEFAULT '1', dom tinyint(4) NOT NULL DEFAULT '1', dow tinyint(4) NOT NULL DEFAULT '1', occurrence tinyint(4) NOT NULL DEFAULT '0', islast tinyint(1) NOT NULL DEFAULT '0' ); +------------+----------+------+-------+-----+-----+------------+--------+ | dt | daycount | year | month | dom | dow | occurrence | islast | +------------+----------+------+-------+-----+-----+------------+--------+ | 2012-02-02 | 734900 | 2012 | 2 | 2 | 5 | 1 | 0 | | 2012-02-03 | 734901 | 2012 | 2 | 3 | 6 | 1 | 0 | | 2012-02-04 | 734902 | 2012 | 2 | 4 | 7 | 1 | 0 | | 2012-02-05 | 734903 | 2012 | 2 | 5 | 1 | 1 | 0 | | 2012-02-06 | 734904 | 2012 | 2 | 6 | 2 | 1 | 0 | | 2012-02-07 | 734905 | 2012 | 2 | 7 | 3 | 1 | 0 | | 2012-02-08 | 734906 | 2012 | 2 | 8 | 4 | 2 | 0 | | 2012-02-09 | 734907 | 2012 | 2 | 9 | 5 | 2 | 0 | +------------+----------+------+-------+-----+-----+------------+--------+ 

---

We are not completely tuned to use the code or table above, any working solutions would be welcome. Please do not tell me:

How could you keep possible recurring times?

What is the best way to simulate recurring events in a calendar application?

Do I have to keep dates or recurrence rules in my database when creating a calendar application?

or

http://tools.ietf.org/html/rfc5545

I checked them, and they were very useful, but not in the way we expect.

TIA

Answer 1

If I don't miss something, the answer is surprisingly simple. I did not understand that UNIONs can be sorted by common columns using an alias, even if these columns are from different tables. So the full request is:

 SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt AS dait ,r.recurring FROM dates d LEFT JOIN recurringtypes r /* if event recurring every week Eg 'Every Monday' */ ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow) /* if event recurring every month Eg 'First Monday, every month' */ OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence) /* if event recurring every last week of month Eg 'Last Monday, every month' */ OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast) LEFT JOIN events e on e.rectypeid = r.rectypeid LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid LEFT JOIN names n ON e.namesid = n.namesid WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01') UNION SELECT e.eventid,n.nameid,n.lastname,n.firstname,e.firstdate AS dait ,'No' as Recurring FROM events e LEFT JOIN names n ON n.names = e.namesid AND e.rectypeid <= 1 WHERE e.firstdate BETWEEN '2012/02/01' AND '2012/05/01' ORDER BY dait ; 

It was pointed out that using a table to search for dates is a risk since the dates end up, which is true, but calculates whether the date is, for example, the first Monday of the month (or the second, or fourth, or perhaps the fourth and last) seems like a more complex bit of SQL code than I want at the moment.

Answer 2

 SELECT DISTINCT(e.eventid),n.nameid,n.firstname,n.lastname,d.dt,r.recurring FROM dates d LEFT JOIN recurringtypes r /* if event recurring every week Eg 'Every Monday' */ ON (r.rectypeid BETWEEN 2 AND 8 AND r.day = d.dow) /* if event recurring every month Eg 'First Monday, every month' */ OR ((r.rectypeid BETWEEN 9 AND 36) AND r.day = d.dow AND r.occurrence = d.occurrence) /* if event recurring every last week of month Eg 'Last Monday, every month' */ OR (r.rectypeid >= 37 AND r.day = d.dow and r.islast = d.islast) LEFT JOIN events e on e.rectypeid = r.rectypeid OR (e.rectypeid <= 1 AND e.eventid IS NOT NULL) LEFT JOIN eventtypes t ON e.eventtypeid = t.eventtypeid LEFT JOIN names n ON e.namesid = n.namesid WHERE (d.dt BETWEEN '2012/02/01' AND '2012/05/01') ORDER BY d.dt;