sql – 显示下一个事件日期

发布时间：2020-05-23 11:59:28 所属栏目：MsSql 来源：互联网

导读：用于存储事件和事件元数据的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

用于存储事件和事件元数据的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,[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

我没有什么东西我想以给定的间隔显示repeat_start之后的所有日期.

示例这里第一个活动开始于(2014年1月3日,上午10点)unixtimestamp = 1388743200,并在每个星期五(7天)继续,我们还计划在星期六(2014年1月1日)1388858400开始的第一个活动,并每7天继续一次(星期六) )

它可以是一个月/每日/等等.所以我们将间隔定义为秒.

如果我给出一些输入,如2014年1月30日,即= 1391040000(2014年1月30日00:00:00)

预期结果

Billa访问,2014年1月3日10 A.M

Billa访问,2014年1月4日10 A.M

Billa Visit,10 Jan 2014 10 A.M

Billa访问,2014年1月11日10 A.M

Billa访问,2014年1月17日10 A.M

Billa访问,2014年1月18日10 A.M

Billa访问,2014年1月24日10 A.M

Billa访问,2014年1月25日10 A.M

SQL FIDDLE LINK

解决方法

你的第一步是让你的事件开始日期与每个事件和重复间隔,这样做你可以使用：

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 – part
1
> Generate a set or sequence without loops – part
2
> Generate a set or sequence without loops – part
3

如果我们将数字表限制在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,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,EventDate = DATEADD(SECOND,n.Number * e.RepeatInterval,e.StartDateTime)
FROM    EventData e
        CROSS JOIN Numbers n
WHERE   DATEADD(SECOND,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,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,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,em.StartDateTime) <= @EndDate
AND (   DATEADD(SECOND,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

这只是我如何实现它的一个非常基本的表示,例如我实际上完全使用任何关于重复数据的查询视图,以便在RepeatDayOfWeek中没有条目的任何事件将被假定为每天重复,而不是从不.随着这个和其他答案中的所有其他细节,你应该希望足够让你开始.

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!