sql - 显示下一个事件日期

Question

用于存储事件和事件元数据的 UI 设计是

SQL 表设计是

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

和

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)

事件数据是

事件元数据为

我关注了Repeating calendar events and some final maths我写了下面的查询

列出给定结束日期之前的所有事件日期

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

我什么也没得到。我想以给定的时间间隔显示重复开始之后的所有日期。

这里的例子第一个事件开始于(2014年1月3日，上午10点)unixtimestamp = 1388743200并持续每周五(7天)，我们还安排第一个事件开始于周六(2014年1月4日)1388858400并每7继续一次天(星期六)

可以是每月一次/每天一次/等等。因此，我们将间隔定义为秒。

如果我提供一些输入，例如 2014 年 1 月 30 日，即 =1391040000 (2014 年 1 月 30 日 00:00:00)

预期结果

比拉访问，2014 年 1 月 3 日上午 10 点

比拉访问，2014 年 1 月 4 日上午 10 点

比拉访问，2014 年 1 月 10 日上午 10 点

比拉访问，2014 年 1 月 11 日上午 10 点

比拉访问，2014 年 1 月 17 日上午 10 点

比拉访问，2014 年 1 月 18 日上午 10 点

比拉访问，2014 年 1 月 24 日上午 10 点

比拉访问，2014 年 1 月 25 日上午 10 点

<强> SQL FIDDLE LINK

Answer 1

您的第一步是获取每个事件的事件开始日期以及重复间隔，为此您可以使用:

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

这给出:

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

要重复此操作，您需要一个用于交叉连接的数字表，如果您没有，有多种方法可以动态生成一个，为简单起见，我将使用:

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

如需进一步阅读，Aaron Bertrand已经对生成连续数字列表的方法进行了一些深入的比较:

• Generate a set or sequence without loops – part1
• Generate a set or sequence without loops – part2
• Generate a set or sequence without loops – part3

如果我们将数字表限制为仅 0 - 5，并且仅查看第一个事件，则交叉连接两个事件将给出:

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

然后，您可以通过将 RepeatInterval * Number 添加到事件开始时间来获取发生次数:

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;

这给出了您的预期输出:

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

<小时/>

我认为你的模式是有问题的，但加入:

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

充其量是脆弱的。我认为您最好将开始日期和与其关联的重复间隔存储在一起:

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

这会将您的数据简化为:

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

它还允许您为重复添加结束日期，即如果您只想重复一周。那么您的查询将简化为:

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

<小时/>

我不会向您提供我过去如何实现这一目标的完整模式，但我会给出一个非常精简的示例，您希望可以从中构建自己的示例。我只会添加每周一至周五发生的事件的示例:

在上面的 ER RepeatEvent 中存储重复事件的基本信息，然后根据重复类型(每日、每周、每月)填充一个或多个其他表。在每周事件的示例中，它将在表 RepeatDay 中存储一周中重复的所有日期。如果需要仅限于某些月份，您可以将这些月份存储在 RepeatMonth 中，依此类推。

然后使用日历表，您可以获得第一个日期之后的所有可能日期，并将这些日期限制为仅与一年中的星期几/月份等匹配的日期:

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

这只是我如何实现它的一个非常基本的表示，例如，我实际上完全使用了对重复数据的任何查询的 View ，因此 RepeatDayOfWeek 中没有条目的任何事件都将被假定为重复每天，而不是从不。除了本答案和其他答案中的所有其他详细信息之外，您应该有足够的信息来帮助您入门。