• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 596
  • Last Modified:

Data model for Recurring Events -> performance

I need to create a data model to store recurring events based on a calendar (date and time on a highly detailed base).
For example, an event is introdfuced on a specific date in the database. This is done once. But, I need to visualize the occurences in the future (and past) based on a complex RecurrencePattern (for example, each day, or every 2 days/weeks/months => daily - weekly or monthly pattern). I need a very fast query that enables me to visualize for a specific period (in the future or the past) to visualize all the available events.

My question is, how to model this in a data model so it is still fast enough to search (over 20.000 events).
What are the good modeling practices? Any examples for a generic solution?
  • 4
  • 3
1 Solution
i'm not sure i understand
you have a record in the table that says an event happens every 2 days,
now you want a query that will return the dates when this event will happen?
erwin_desAuthor Commented:
Sort of...
I'm looking for a good implementation model where i can easily store Recurrent Events providing me the ability to extract in a very fast way (!!!!), all the events for a specific (future or past) timespan.

It is the model I want to construct.

this is what i have in mind:

have a table with numbers:

create table numbers (num int);

declare @i int
set @i = -300

while (@i <= 300)
      insert into numbers values (@i)
      set @i = @i + 1

and create a table for your events, and store the interval for that event
create table events (event_name varchar(100),
    interval_amount int , interval_type varchar(100))

now, when you want to generate the future events, try something like this:
 case interval_type
  when 'day' then dateadd(day, t2.num*t1.interval_amount, getdate())
  when 'month' then dateadd(month, t2.num*t1.interval_amount, getdate())
  when 'year' then dateadd(year, t2.num*t1.interval_amount, getdate())
 end as next_date
from events t1 cross join numbers t2
where t2.num between -50 and 50

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

i checked it, and it is very fast
erwin_desAuthor Commented:
I was already at this solution which works fine for the part of the days, but the problem is that the model I use has more details on the planning level for weeks and months.
- For weeks : it should be possible to select one or more specific days in the week like mon, thu, fr ...
For example : every week (or even every 2 weeks) , only on mon, thu, fri, sat.
- For months : there also it should be  possible to select a specific day(number) in the month or every (specific) weekday every first, second ... week in the month. For example  (1): every month (or even every 2 months) but on the 23rd day only ; (2) every month , every second thursday on the month.

If i arrive to construct a fast query for week and month separately and use a union on both, I have the solution.
you can have another table indicating the limitations you specified and join that table as well
erwin_desAuthor Commented:
Thanks ! I'm now trying to do some performance tuning on my query. I will post my solution and hope I can count on your expertise to fine-tune my solution.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now