Display next event date

Question

The UI design for storing event and event meta data is

SQL TABLE DESIGN is

CREATE TABLE [dbo].[EVENTS]
([ID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [nvarchar](255) NOT NULL)

and

CREATE TABLE [dbo].[EVENTS_META](
[ID] [int] IDENTITY(1,1) NOT NULL,
[event_id] [int] NOT NULL,
[meta_key] [varchar](255) NOT NULL,
[meta_value] [bigint] NOT NULL)

The Events data is

Event Metadata is

I Followed Repeating calendar events and some final maths and I wrote the below query

LIST ALL THE EVENT DATES BEFORE THE GIVEN END DATE

SELECT EV.*
FROM events AS EV
RIGHT JOIN events_meta AS EM1 ON EM1.event_id = EV.id
RIGHT JOIN events_meta AS EM2 ON EM2.meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100))
WHERE EM1.meta_key = 'repeat_start'
AND ((1391040000 - EM1.meta_value ) % EM2.meta_value) = 0

I am not getting anything. I want to display all dates after repeat_start with the given interval.

Example here 1st event starts on (3rd Jan 2014, 10 A.M) unixtimestamp =1388743200 and continues every friday(7 days), we also schedule the first event on starts saturday(Jan04, 2014)1388858400 and continues once in every 7 days(saturday)

It can be once in a month/daily/etc. So we have the interval defined as seconds.

If i give some input like 30 Jan 2014 , i.e =1391040000 (30 Jan 2014 00:00:00)

Expected Result

Billa Visit, 3 Jan 2014 10 A.M

Billa Visit, 4 Jan 2014 10 A.M

Billa Visit, 10 Jan 2014 10 A.M

Billa Visit, 11 Jan 2014 10 A.M

Billa Visit, 17 Jan 2014 10 A.M

Billa Visit, 18 Jan 2014 10 A.M

Billa Visit, 24 Jan 2014 10 A.M

Billa Visit, 25 Jan 2014 10 A.M

SQL FIDDLE LINK

Answer 1

Your first step is to get your event start dates with each event, and the repeat interval, to do this you can use:

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));

This gives:

EventID | Name         | StartDateTime       | RepeatInterval
--------+--------------+---------------------+-----------------
   1    | Billa Vist   | 2014-01-03 10:00:00 |     604800
   1    | Billa Vist   | 2014-01-04 18:00:00 |     604800

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:

WITH Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  Number
FROM    Numbers;

For further reading, Aaron Bertrand has done some in depth comparisons ways of generating sequential lists of numbers:

• Generate a set or sequence without loops – part 1
• Generate a set or sequence without loops – part 2
• Generate a set or sequence without loops – part 3

If we limit our numbers table to only 0 - 5, and only look at the first event, cross joining the two will give:

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

Then you can get your occurance by adding RepeatInterval * Number to the event start time:

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;

This gives your expected output:

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

Example on SQL Fiddle

---

I think the schema you have is questionable though, the join on:

Meta_Key = 'repeat_interval_' + CAST(rs.ID AS VARCHAR(10))

is flimsy at best. I think you would be much better off storing the start date and repeat interval associated with it together:

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)
);

This would simplify your data to:

EventID | StartDateTime       | RepeatInterval | RepeatEndDate
--------+---------------------+----------------+---------------
   1    | 2014-01-03 10:00:00 |    604800      |     NULL
   1    | 2014-01-04 18:00:00 |    604800      |     NULL

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:

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;

Example on SQL Fiddle

---

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:

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 RepeatDay. If this needed to be limited to only certain months, you could store these months in RepeatMonth, and so on.

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:

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);

Example on SQL Fiddle

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 RepeatDayOfWeek 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.

Answer 2

The following will generate events based on StartEvent and MEta description with a CTE.

Change the values for MaxDate and MaxEvents according to parameters values.

declare @MaxDate datetime = convert(datetime,'12/2/2014', 101);
declare @MaxEvents integer=  200;

; With
    -- number generator by power of 2
    n2(n) as ( select 1 as n union all select 1),
    n4(n) as ( select 1 from n2 t1 cross join n2 t2 ),
    n16(n) as ( select 1 from n4 t1 cross join n4 t2 ),
    n256(n) as ( select 1 from n16 t1 cross join n16 t2 ),
    n65k(n) as ( select 1 from n256 t1 cross join n256 t2 ),
   Numbers (n) as (select row_number() over( order by n) from n65k ),

    -- Start of events 
    StartEvents as 
    ( SELECT 1 as EventNo, EV.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value,convert(datetime,'01/01/1970', 101)) as EventDate
        FROM events AS EV
        INNER JOIN events_meta  EM 
          ON EM.event_id = EV.id
          AND EM.meta_key = 'repeat_start'),
    -- Repeating events N times
    NextEvents AS
    ( SELECT Numbers.N+1 asEventNo, StartEvents.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value*Numbers.n,StartEvents.EventDate) as EventDate
        FROM StartEvents 
        INNER JOIN  events_meta EM 
            ON EM.event_id = StartEvents.event_id
            AND  EM.meta_key = 'repeat_interval_'+ ltrim(rtrim(str(StartEvents.ID )))
            AND ((1391040000- EM.meta_value ) % EM.meta_value) = 0 
     cross join Numbers 
     -- not to overflow (dateadd parameter is int type)
     Where Numbers.N < 3000
    )
   -- startEvents union nextEvents
  select EventNo, Name, Meta_key, EventDate
  FROM (
        Select * from StartEvents
        union all select * from NextEvents ) AllEvents
  where EventDate < @MaxDate
   and  EventNo < @MaxEvents
        order by ID ;

Answer 3

One way of accomplishing this is by using a loop and inserting a record into a temp table. Then you can simply select from your temp table. In the example below I saved off both the unixtimestamp value as well as that value converted to datetime.

declare @enddate bigint, @intervalFactor int, @rowresult int

set @enddate = 1391040000

create table #Results
(
  eventid int,
  eventdate bigint,
  eventdatedate datetime
)

set @rowresult = 1
set @intervalFactor = 0

WHILE (@rowresult > 0)
BEGIN
  Insert #Results (eventid, eventdate, eventdatedate)
  Select events.id, date.meta_value + (intrvl.meta_value * @intervalFactor)
        ,DATEADD(ss,date.meta_value + (intrvl.meta_value * @intervalFactor), CAST('1970-01-01 00:00:00' AS datetime))
      from events
      inner join events_meta date
        on events.id = date.event_id
        AND date.meta_key = 'repeat_start'
      inner join events_meta intrvl
        on events.id = intrvl.event_id
        and intrvl.meta_key = 'repeat_interval_'+ CAST(date.id as Varchar(100))
  where ((@enddate - date.meta_value ) % intrvl.meta_value) >= 0
  and date.meta_value + (intrvl.meta_value * @intervalFactor) <= @enddate


  set @rowresult = @@rowcount
  set @intervalFactor = @intervalFactor + 1

END  

select * from #Results

Another possible solution for this problem would to utilize a recursive CTE.