显示下一个事件日期 [英] Display next event date
问题描述
用于存储事件和事件元数据的 UI 设计是
SQL 表设计是
创建表 [dbo].[事件]([ID] [int] IDENTITY(1,1) 非空，[名称] [nvarchar](255) 非空)和
创建表 [dbo].[EVENTS_META]([ID] [int] IDENTITY(1,1) 非空，[event_id] [int] 非空，[meta_key] [varchar](255) 非空，[meta_value] [bigint] NOT NULL)事件数据是
事件元数据是
我遵循了
这只是我如何实现它的一个非常基本的表示，例如，我实际上完全使用了视图对重复数据的任何查询，以便任何在 RepeatDayOfWeek 中没有条目的事件都将被假定为重复每天，而不是永远.连同此答案和其他答案中的所有其他详细信息，希望您有足够的信息来帮助您入门.
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
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
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;
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);
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.
这篇关于显示下一个事件日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
