StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Your first step is to get your event start dates with each event, and the repeat interval, to do this you can use:</p> <pre><code>SELECT EventID = e.ID, e.Name, StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'), RepeatInterval = ri.Meta_Value FROM dbo.Events e INNER JOIN dbo.Events_Meta rs ON rs.Event_ID = e.ID AND rs.Meta_Key = 'repeat_start' INNER JOIN dbo.Events_Meta ri ON ri.Event_ID = e.ID AND ri.Meta_Key = 'repeat_interval_' + CAST(e.ID AS VARCHAR(10)); </code></pre> <p>This gives:</p> <pre><code>EventID | Name | StartDateTime | RepeatInterval --------+--------------+---------------------+----------------- 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 1 | Billa Vist | 2014-01-04 18:00:00 | 604800 </code></pre> <p>To get this to repeat you will need a numbers table to cross join to, if you don't have one there are a number of ways to generate one on the fly, for simplicity reasons I will use:</p> <pre><code>WITH Numbers AS ( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1 FROM sys.all_objects a ) SELECT Number FROM Numbers; </code></pre> <p>For further reading, <a href="https://stackoverflow.com/users/61305/aaron-bertrand">Aaron Bertrand</a> has done some in depth comparisons ways of generating sequential lists of numbers:</p> <ul> <li><a href="http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1" rel="nofollow noreferrer">Generate a set or sequence without loops – part 1</a></li> <li><a href="http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2" rel="nofollow noreferrer">Generate a set or sequence without loops – part 2</a></li> <li><a href="http://www.sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3" rel="nofollow noreferrer">Generate a set or sequence without loops – part 3</a></li> </ul> <p>If we limit our numbers table to only 0 - 5, and only look at the first event, cross joining the two will give:</p> <pre><code>EventID | Name | StartDateTime | RepeatInterval | Number --------+--------------+---------------------+----------------+--------- 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 0 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 1 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 2 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 3 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 4 1 | Billa Vist | 2014-01-03 10:00:00 | 604800 | 5 </code></pre> <p>Then you can get your occurance by adding <code>RepeatInterval * Number</code> to the event start time:</p> <pre><code>DECLARE @EndDate DATETIME = '20140130'; WITH EventData AS ( SELECT EventID = e.ID, e.Name, StartDateTime = DATEADD(SECOND, rs.Meta_Value, '19700101'), RepeatInterval = ri.Meta_Value FROM dbo.Events e INNER JOIN dbo.Events_Meta rs ON rs.Event_ID = e.ID AND rs.Meta_Key = 'repeat_start' INNER JOIN dbo.Events_Meta ri ON ri.Event_ID = e.ID AND ri.Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10)) ), Numbers AS ( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1 FROM sys.all_objects a ) SELECT e.EventID, e.Name, EventDate = DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime) FROM EventData e CROSS JOIN Numbers n WHERE DATEADD(SECOND, n.Number * e.RepeatInterval, e.StartDateTime) < @EndDate ORDER BY e.EventID, EventDate; </code></pre> <p>This gives your expected output:</p> <pre><code>EVENTID | NAME | EVENTDATE --------+---------------+-------------------------------- 1 | Billa Vist | January, 03 2014 10:00:00+0000 1 | Billa Vist | January, 04 2014 18:00:00+0000 1 | Billa Vist | January, 10 2014 10:00:00+0000 1 | Billa Vist | January, 11 2014 18:00:00+0000 1 | Billa Vist | January, 17 2014 10:00:00+0000 1 | Billa Vist | January, 18 2014 18:00:00+0000 1 | Billa Vist | January, 24 2014 10:00:00+0000 1 | Billa Vist | January, 25 2014 18:00:00+0000 </code></pre> <p><strong><a href="http://sqlfiddle.com/#!6/44057/1" rel="nofollow noreferrer">Example on SQL Fiddle</a></strong></p> <hr> <p>I think the schema you have is questionable though, the join on:</p> <pre><code>Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10)) </code></pre> <p>is flimsy at best. I think you would be much better off storing the start date and repeat interval associated with it together:</p> <pre><code>CREATE TABLE dbo.Events_Meta ( ID INT IDENTITY(1, 1) NOT NULL, Event_ID INT NOT NULL, StartDateTime DATETIME2 NOT NULL, IntervalRepeat INT NULL, -- NULLABLE FOR SINGLE EVENTS RepeatEndDate DATETIME2 NULL, -- NULLABLE FOR EVENTS THAT NEVER END CONSTRAINT PK_Events_Meta__ID PRIMARY KEY (ID), CONSTRAINT FK_Events_Meta__Event_ID FOREIGN KEY (Event_ID) REFERENCES dbo.Events (ID) ); </code></pre> <p>This would simplify your data to:</p> <pre><code>EventID | StartDateTime | RepeatInterval | RepeatEndDate --------+---------------------+----------------+--------------- 1 | 2014-01-03 10:00:00 | 604800 | NULL 1 | 2014-01-04 18:00:00 | 604800 | NULL </code></pre> <p>It also allows you to add an end date to your repeat, i.e. if you only want it to repeat for one week. This then your query simlifies to:</p> <pre><code>DECLARE @EndDate DATETIME = '20140130'; WITH Numbers AS ( SELECT Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1 FROM sys.all_objects a ) SELECT e.ID, e.Name, EventDate = DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) FROM Events e INNER JOIN Events_Meta em ON em.Event_ID = e.ID CROSS JOIN Numbers n WHERE DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= @EndDate AND ( DATEADD(SECOND, n.Number * em.IntervalRepeat, em.StartDateTime) <= em.RepeatEndDate OR em.RepeatEndDate IS NULL ) ORDER BY EventDate; </code></pre> <p><strong><a href="http://sqlfiddle.com/#!6/2d8c0/4" rel="nofollow noreferrer">Example on SQL Fiddle</a></strong></p> <hr> <p>I won't give you my full schema for how I have achieved this in the past, but I will give a very cut down example, from which you can hopefully build your own. I will only add an example for an event that occurs weekly on Mon-Fri:</p> <p><img src="https://i.stack.imgur.com/Br1hM.png" alt="enter image description here"></p> <p>In the above ER RepeatEvent stores the basic information for the recurring event, then depending on the repeat type (Daily, weekly, monthly) one or more of the other tables is populated. In example of a weekly event, it would store all the days of the week that it repeats in in the table <code>RepeatDay</code>. If this needed to be limited to only certain months, you could store these months in <code>RepeatMonth</code>, and so on.</p> <p>Then using a calendar table you can get all the possible dates after the first date, and limit these to only those dates that match the day of the week/month of the year etc:</p> <pre><code>WITH RepeatingEvents AS ( SELECT e.Name, re.StartDateTime, re.EndDateTime, re.TimesToRepeat, RepeatEventDate = CAST(c.DateKey AS DATETIME) + CAST(re.StartTime AS DATETIME), RepeatNumber = ROW_NUMBER() OVER(PARTITION BY re.RepeatEventID ORDER BY c.Datekey) FROM dbo.Event e INNER JOIN dbo.RepeatEvent re ON e.EventID = re.EventID INNER JOIN dbo.RepeatType rt ON rt.RepeatTypeID = re.RepeatTypeID INNER JOIN dbo.Calendar c ON c.DateKey >= re.StartDate INNER JOIN dbo.RepeatDayOfWeek rdw ON rdw.RepeatEventID = re.RepeatEventID AND rdw.DayNumberOfWeek = c.DayNumberOfWeek WHERE rt.Name = 'Weekly' ) SELECT Name, StartDateTime, RepeatEventDate, RepeatNumber FROM RepeatingEvents WHERE (TimesToRepeat IS NULL OR RepeatNumber <= TimesToRepeat) AND (EndDateTime IS NULL OR RepeatEventDate <= EndDateTime); </code></pre> <p><strong><a href="http://sqlfiddle.com/#!3/37618/1" rel="nofollow noreferrer">Example on SQL Fiddle</a></strong></p> <p>This is only a very basic representation of how I implemented it, for instance I actually used entirely views any query for the repeating data so that any event with no entries in <code>RepeatDayOfWeek</code> would be assumed to repeat every day, rather than never. Along with all the other detail in this and other answers, you should hopefully have more than enough to get you started.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload