sql – 显示下一个事件日期

前端之家收集整理的这篇文章主要介绍了sql – 显示下一个事件日期前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
用于存储事件和事件元数据的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中没有条目的任何事件将被假定为每天重复,而不是从不.随着这个和其他答案中的所有其他细节,你应该希望足够让你开始.

猜你在找的MsSQL相关文章