用于存储事件和事件元数据的UI设计是
SQL TABLE DESIGN现为
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)
事件数据是
事件元数据为
我遵循了重复日历事件和一些最终数学的操作,并编写了以下查询
在给定的结束日期之前列出所有事件日期
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
我什么都没收到。我想以给定的间隔显示repeat_start之后的所有日期。
此处的示例第一个事件开始于(2014年1月3日,上午10点)unixtimestamp = 1388743200,并在每个星期五(7天)持续,我们也将第一个事件安排在星期六(2014年1月4日)1388858400开始,并每7天(星期六)持续一次)
它可以是每月一次/每天一次等。因此,我们将其interval
定义为秒。
如果我输入一些内容,例如2014年1月30日,i.e =1391040000
(2014年1月30日00:00:00)
预期结果
Billa Visit,2014年1月3日上午10点
Billa Visit,2014年1月4日上午10点
Billa Visit,2014年1月10日上午10点
Billa Visit,2014年1月11日,上午10点
Billa Visit,2014年1月17日,上午10点
Billa Visit,2014年1月18日上午10点
Billa Visit,2014年1月24日上午10点
Billa Visit,2014年1月25日上午10点
第一步是获取每个事件的事件开始日期以及重复间隔,为此,您可以使用:
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做了一些深度比较方法来生成数字顺序列表:
如果我们将数字表限制为仅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
我认为您所拥有的模式是有问题的,请加入:
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;
我不会为您提供过去如何实现此目标的完整方案,但我将举一个非常简单的示例,希望您可以从中建立自己的示例。我将仅添加一个在每周一至周五发生的事件的示例:
在上面的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);
这只是我实现方式的非常基本的表示,例如,我实际上完全查看了重复数据的任何查询,因此,RepeatDayOfWeek
假定没有条目的任何事件每天都在重复,而不是从不重复。除了本答案和其他答案中的所有其他详细信息之外,您还应该有足够的资源来入门。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句