如何存储和查询日程数据？ [英] How can I store and query schedule data?
问题描述
我想允许我的用户为他们的事件设置一个时间表。这可能是一天，或为了方便，我想允许他们指定一个重复发生的事件（类似于Outlook约会）。
I'd like to allow my users to setup a schedule for their events. It could be a single day, or for convenience I'd like to allow them to specify a reoccurring event (similar to an Outlook appointment).
对于单个事件似乎很容易（伪代码）：
For the single event it seems pretty easy (pseudo-code):
只要有一个DateOfEvent列，就可以有日期。
Just have a DateOfEvent column that has the date on it.
抓住未来的事件：
从DateOfEvent> {DateTime.Now}
Select * from events where DateOfEvent > {DateTime.Now}
的事件中选择*我可以存储和查询重复发生的事件吗？我不需要做时间，因为我只是分开存储，如果他们需要不同的时间，我会让他们创造另一个事件。所以不：每个星期五在5和thursdays在3。
But how could I store and query a reoccurring event? I don't need to do times, as I'd just store that seperately, and if they needed a different time I'd just have them create another event. So no: Every wednesday at 5 and thursdays at 3.
示例：
每个星期一， ，thu，星期五，每周
Every mon, tues, wed, thu, fri, every week
每周wed每周
每月第二个星期二
我用来查询
我添加了一个开始和结束日期。如果用户选择了单个日期，我将开始日期和结束日期设置为所选日期。我只需要修改答案的代码。
I added a start and end dates. If the user selected a single date I set both start and end dates to the chosen date. I just had to modify the answer's code a bit.
DECLARE
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME;
SELECT
@StartDate = '20091129',
@EndDate = '20101220';
SELECT
d.CurrentDate,
m.*
FROM
Calendar AS d
INNER JOIN Meet AS m
ON
(
(d.CurrentDate = m.StartDate AND d.CurrentDate = m.EndDate)
OR d.DaysOfTheMonth = m.DayOfTheMonth
OR (d.DaysOfTheWeek = m.DayOfTheWeek AND COALESCE(m.WeekOfTheMonth, d.WeekOfTheMonth) = d.WeekOfTheMonth)
OR d.DaysOfTheWeek IN (1,7) AND m.OnWeekends = 1
OR d.DaysOfTheWeek BETWEEN 2 AND 6 AND m.OnWeekDays = 1
)
-- now you can inner join to the event table
-- to list the name, other details, etc.
WHERE
d.CurrentDate BETWEEN @StartDate AND @EndDate
AND d.CurrentDate BETWEEN m.StartDate AND m.EndDate
ORDER BY d.CurrentDate;
GO
填写Calandar表：
Filling in the Calandar table:
INSERT dbo.Calendar(CurrentDate, DaysOfTheMonth, DaysOfTheWeek, WeekOfTheMonth, IsWeekDay, Even)
SELECT
CurrentDate, DaysOfTheMonth, DaysOfTheWeek,
WeekOfTheMonth = DATEDIFF(WEEK, DATEADD(DAY, 1-DaysOfTheMonth, CurrentDate), CurrentDate) + 1,
CASE WHEN DaysOfTheWeek IN (1, 7) THEN 0 ELSE 1 END AS IsWeekDay,
CASE WHEN w % 2 = 1 THEN 0 ELSE 1 END AS Even
FROM
(
SELECT
CurrentDate,
DaysOfTheMonth = DATEPART(DAY, CurrentDate),
DaysOfTheWeek = DATEPART(WEEKDAY, CurrentDate),
w = DATEPART(WEEK, CurrentDate)
FROM
(
SELECT CurrentDate = DATEADD(DAY, n-1, '20141001')
FROM
(
SELECT TOP 900
n = ROW_NUMBER() OVER (ORDER BY c1.[object_id])
FROM sys.all_columns AS c1
CROSS JOIN sys.all_columns AS c2
ORDER BY n
) AS x) AS y) AS z;
推荐答案
你可以这样做：
CREATE TABLE dbo.EventSchedule
(
EventID INT, -- FOREIGN KEY to event details
EventDate SMALLDATETIME, -- if one-time event
DayOfMonth TINYINT, -- if once a month, e.g. 3rd of every month
DayOfWeek TINYINT, -- if once a week, e.g. every Tuesday = 3
WeekDays BIT, -- if only on weekdays, e.g. 1 = mon-fri
Weekends BIT, -- if only on weekends, e.g. 1 = sat-sun
-- the next two are combined, e.g. 2/2 = 2nd Monday of each month
MonthlyInstance TINYINT,
MonthlyWeekday TINYINT
);
那么如果你想查找一个在给定日期发生的所有事件，你可以做：
So then if you wanted to find all the events scheduled to happen on a given date, you could do:
DECLARE
@dt SMALLDATETIME,
@dm TINYINT,
@dw TINYINT,
@inst TINYINT;
SELECT
@dt = '20091201',
@dm = DATEPART(DAY, @dt)
@dw = DATEPART(WEEKDAY, @dt),
@inst = DATEDIFF(WEEK, DATEADD(DAY, 1-@dm, @dt), @dt) + 1;
SELECT EventID
FROM dbo.EventSchedule
WHERE EventDate = @dt
OR DayOfMonth = @dm
OR DayOfWeek = @dw
OR (Weekdays = 1 AND @dw BETWEEN 2 AND 6)
OR (Weekends = 1 AND @dw IN (1,7))
OR (MonthlyInstance = @inst AND MonthlyWeekday = @dw);
这是未经测试的（我从感恩节的Mac发布，毕竟），依赖于SET DATEFIRST是默认值（其中Sunday = 1，Monday = 2等）。我对第二个星期二的部分也没有100％的信心，因为这可能需要一些额外的数学，取决于本月的第一天的工作日。但是我认为这可以让你开始其他的部分，当我有机会的时候我会回来重新审视。
This is untested (I am posting from a Mac on Thanksgiving, after all), and relies on SET DATEFIRST being the default (where Sunday = 1, Monday = 2, etc). I'm also not 100% confident on the 2nd Tuesday part, as it likely needs some additional math depending on the weekday of the first day of the month. But I thought this could give you a start for the other parts, and I'll come back and revisit when I have a chance.
对于一些更为复杂的选择，请参阅： http://www.codeproject.com/KB/database/sqlscheduleselector.aspx
For some much more complicated options, see: http://www.codeproject.com/KB/database/sqlscheduleselector.aspx
这里是一个更完整的例子，其中包含一个填充的日历表，一些不同类型的示例事件，以及一个检索所有预期事件的查询一个日期范围。
And here is a more complete example, with a populated calendar table, some sample events of different types, and a query that retrieves all of the expected events given a date range.
CREATE DATABASE test;
GO
USE test;
GO
SET NOCOUNT ON;
GO
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME PRIMARY KEY,
dm TINYINT,
dw TINYINT,
mw TINYINT,
wd BIT
);
GO
-- populate the table with the days from 2009
-- (you will want more obviously; change TOP or add WHERE)
INSERT dbo.Calendar(dt, dm, dw, mw, wd)
SELECT
dt, dm, dw,
mw = DATEDIFF(WEEK, DATEADD(DAY, 1-dm, dt), dt) + 1,
CASE WHEN dw IN (1, 7) THEN 0 ELSE 1 END
FROM
(
SELECT
dt,
dm = DATEPART(DAY, dt),
dw = DATEPART(WEEKDAY, dt)
FROM
(
SELECT dt = DATEADD(DAY, n-1, '20090101')
FROM
(
SELECT TOP 365
n = ROW_NUMBER() OVER (ORDER BY c1.[object_id])
FROM sys.all_columns AS c1
CROSS JOIN sys.all_columns AS c2
ORDER BY n
) AS x) AS y) AS z;
GO
-- create your schedule table:
CREATE TABLE dbo.EventSchedule
(
EventID INT, -- FOREIGN KEY to event details
EventDate SMALLDATETIME, -- if one-time event
[DayOfMonth] TINYINT, -- if once a month, e.g. 3rd of every month
[DayOfWeek] TINYINT, -- if once a week, e.g. every Tuesday = 3
Weekdays BIT, -- if only on weekdays, e.g. 1 = mon-fri
Weekends BIT, -- if only on weekends, e.g. 1 = sat-sun
-- if you want every day, set Weekdays+Weekends = 1
WeekOfMonth TINYINT -- if only the nth Tuesday etc.
);
-- create some events:
INSERT dbo.EventSchedule
(
EventID,
EventDate,
[DayOfMonth],
[DayOfWeek],
Weekdays,
Weekends,
WeekOfMonth
)
-- one on Jan 5th:
SELECT 1, '20090105', NULL, NULL, NULL, NULL, NULL
-- one on the 3rd of every month:
UNION ALL SELECT 2, NULL, 3, NULL, NULL, NULL, NULL
-- one every Tuesday:
UNION ALL SELECT 3, NULL, NULL, 3, NULL, NULL, NULL
-- one the 2nd Wednesday of each month:
UNION ALL SELECT 4, NULL, NULL, 4, NULL, NULL, 2
-- one only on weekends:
UNION ALL SELECT 5, NULL, NULL, NULL, NULL, 1, NULL
-- one only on weekdays:
UNION ALL SELECT 6, NULL, NULL, NULL, 1, NULL, NULL;
-- now, we have 6 events, some of which will happen
-- multiple times in any given date range. So let's
-- try it:
DECLARE
@StartDate SMALLDATETIME,
@EndDate SMALLDATETIME;
SELECT
@StartDate = '20090101',
@EndDate = '20090108';
SELECT
d.dt,
e.EventID
FROM
dbo.Calendar AS d
INNER JOIN dbo.EventSchedule AS e
ON
(
d.dt = e.EventDate
OR d.dm = e.[DayOfMonth]
OR (d.dw = e.[DayOfWeek] AND COALESCE(e.WeekOfMonth, d.mw) = d.mw)
OR d.dw IN (1,7) AND e.Weekends = 1
OR d.dw BETWEEN 2 AND 6 AND e.Weekdays = 1
)
-- now you can inner join to the event table
-- to list the name, other details, etc.
WHERE
d.dt BETWEEN @StartDate AND @EndDate
ORDER BY d.dt, e.EventID;
GO
DROP TABLE dbo.Calendar, dbo.EventSchedule;
GO
USE [master];
GO
DROP DATABASE test;
GO
这篇关于如何存储和查询日程数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
