我怎么能存储和查询调度数据？ [英] How can I store and query schedule data?
问题描述
我希望让我的用户设置一个时间表，他们的活动。这可能是一个单一的一天，或为方便起见，我想，让他们到指定的事件再次发生（类似于Outlook约会）。
有关的单一事件它似乎很容易的（伪代码）：
只是有上有日起DateOfEvent列
要抓住未来事件：
SELECT * FROM事件，其中DateOfEvent> {} DateTime.Now
但如何我可以存储和查询事件再次发生？我并不需要做的时候，因为我只是存储分开，如果他们需要不同的时间，我只希望让他们创造另一个事件。因此，没有：每逢星期三5和周四下午3
例如：
每星期一，星期二，星期三，周四，周五，每周
每星期三每周
每个月的第二个星期二
我用来查询
我添加了一个开始和结束日期。如果用户选择一个日期我同时设置开始和结束日期到选定的日期。我不得不修改答案的代码位。
定义
@StartDate SMALLDATETIME，
@EndDate SMALLDATETIME;
选择
@StartDate ='20091129'，
@EndDate ='20101220';
选择
d.CurrentDate，
M *
起价
日历为D
INNER JOIN相约为M
ON
（
（d.CurrentDate = m.StartDate AND d.CurrentDate = m.EndDate）
或d.DaysOfTheMonth = m.DayOfTheMonth
或（d.DaysOfTheWeek = M。 DayOfTheWeek和接合（m.WeekOfTheMonth，d.WeekOfTheMonth）= d.WeekOfTheMonth）
或d.DaysOfTheWeek IN（1,7）和m.OnWeekends = 1
或d.DaysOfTheWeek介于2和6 m.OnWeekDays = 1
）
- 现在你可以内部连接到事件表
- 要列出名字，其他细节等
，其中
d.CurrentDate之间@StartDate和@EndDate
和d.CurrentDate之间m.StartDate和m.EndDate
ORDER BY d.CurrentDate;
GO
填补了Calandar表：
INSERT dbo.Calendar（的currentdate，DaysOfTheMonth，类daysOfTheWeek，WeekOfTheMonth，IsWeekDay，即使）
选择
的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当W％2 = 1 THEN 0 ELSE 1 END因为即使
起价
（
选择
的currentdate，
DaysOfTheMonth = DATEPART（DAY，的currentdate），
类daysOfTheWeek = DATEPART（星期，的currentdate），$ b $带宽= DATEPART（一周的currentdate）
起价
（
选择的currentdate = DATEADD（DAY，正1，'20141001'）
起价
（
选择TOP 900 $ b $ =亿ROW_NUMBER（）OVER（ORDER BY C1 [OBJECT_ID]）
从sys.all_columns AS C1
CROSS JOIN sys.all_columns AS C2
ORDER BY N
）为x）为Y）为：Z;
您可以做这样的事情：
CREATE TABLE dbo.EventSchedule
（
事件ID INT - 外键事件的详细信息
EVENTDATE SMALLDATETIME - 如果一次性事件
DAYOFMONTH TINYINT， - 如果每月一次，例如每月
DAYOFWEEK TINYINT 3日， - 如果每周一次，例如每星期二= 3
平日BIT， - 如果仅在工作日，如1 =周一至周五
周末BIT， - 如果只在周末，如1 =周六，周日
- 接下来的两个是组合在一起，例如2/2 =第2每个月
MonthlyInstance TINYINT，
MonthlyWeekday TINYINT
）周一;
如果你想找到安排在指定的日期发生的所有事件
那么，你可以做
定义
@dt SMALLDATETIME，
@dm TINYINT，
@dw TINYINT，
@inst TINYINT;
选择
@dt ='20091201'，
@dm = DATEPART（DAY，@dt）
@dw = DATEPART（星期，@dt），
@inst = DATEDIFF（WEEK，DATEADD（DAY，1 @ DM，@dt），@dt）+ 1;
选择事件ID
FROM dbo.EventSchedule
其中EVENTDATE = @dt
或DAYOFMONTH = @dm
或DAYOFWEEK = @dw
或（平日= 1 AND 2到6之间@dw）
或（周末= 1和@dw（1,7））
或（MonthlyInstance = @inst AND MonthlyWeekday = @dw）;
这是未经测试（我是从感恩节在Mac发布，毕竟），并依靠SET DATEFIRST为默认（其中，星期日= 1，星期一= 2，等等）。我还对第二个星期二部分信心不是100％，因为它可能需要根据每月的第一天工作日一些额外的数学。但我认为这可以给你一个开端其他部分，我会回来，重温当我有机会的。
对于一些更复杂的选项，请参阅： http://www.codeproject.com/KB/database/sqlscheduleselector.aspx
这是一个比较完整的例子，用填充万年历表，不同类型的一些示例事件，以及检索所有给定预期的事件查询。日期范围
CREATE DATABASE试验;
GO
使用的测试;
GO
SET NOCOUNT ON;
GO
CREATE TABLE dbo.Calendar
（
DT SMALLDATETIME PRIMARY KEY，
DM TINYINT，
DW TINYINT，
MW TINYINT，
WD位
）;
GO
- 填充表从2009年
的日子 - （你将要更为明显; TOP更改或添加WHERE）
INSERT dbo.Calendar（DT，DM，DW，MW，WD）
选择
DT，DM，DW，
MW = DATEDIFF（WEEK，DATEADD（DAY，1-DM，DT） ，DT）+ 1，
CASE WHEN在DW（1,7）THEN 0 ELSE 1 END
起价
（
选择
DT，
DM = DATEPART（DAY，DT），
DW = DATEPART（星期，DT）
起价
（
选择DT = DATEADD（DAY，N-1，'20090101'）
起价
（
选择TOP 365 $ b $ =亿ROW_NUMBER（）OVER（ORDER BY C1 [OBJECT_ID]）
从sys.all_columns在C1
CROSS JOIN sys.all_columns AS C2
ORDER BY N
）为x）为Y）为：Z;
GO
- 创建您的日程安排表：
CREATE TABLE dbo.EventSchedule
（
事件ID INT - 外键活动详情
EVENTDATE SMALLDATETIME， - 如果一次性事件
[DAYOFMONTH] TINYINT， - 如果每月一次，例如每月
[DAYOFWEEK] TINYINT 3日， - 如果一旦一个星期，例如每星期二= 3
平日BIT， - 如果仅在工作日，如1 =周一至周五
周末BIT， - 如果只在周末，如1 =周六，周日
- 如果你想天天设置平日周末+ 1 =
WeekOfMonth TINYINT - 如果仅仅是第n个星期二等等
）;
- 创建一些事件：
将dbo.EventSchedule
（
事件ID，
EVENTDATE，
[DAYOFMONTH]，
〔DAYOFWEEK]，
平日里，
周末，
WeekOfMonth
）
- 一个在1月5：
选择1，'20090105'，NULL ，NULL，NULL，NULL，NULL
- 一个在每个月的第三个：
UNION ALL SELECT 2，NULL，3，NULL，NULL，NULL，NULL
- 一个每星期二：
UNION ALL SELECT 3，NULL，NULL，3，NULL，NULL，NULL
- 一个在每个月的第二个星期三：
UNION ALL SELECT 4，NULL，NULL，4，NULL，NULL，2
- 一个只在周末：
UNION ALL SELECT 5，NULL，NULL，NULL，NULL， 1，NULL
- 一个只在工作日：
UNION ALL SELECT 6，NULL，NULL，NULL，1，NULL，NULL;
- 现在，我们有6个事件，其中的一些会发生
- 多次在任何给定的时间范围。因此，让我们
- 试试吧：
DECLARE
@StartDate SMALLDATETIME，
@EndDate SMALLDATETIME;
选择
@StartDate ='20090101'，
@EndDate ='20090108';
选择
d.dt，
e.EventID
起价
dbo.Calendar为D
INNER JOIN dbo.EventSchedule为E
在
（
d.dt = e.EventDate
或d.dm = E [DAYOFMONTH]
或（d.dw = E [DAYOFWEEK]和COALESCE（e.WeekOfMonth，d.mw）= d.mw）
或IN d.dw（1,7）和e.Weekends = 1
或d.dw 2至6和E。平日= 1
）
- 现在你可以内部连接到事件表
- 要列出名字，其他细节等
，其中
D。 DT @StartDate和@EndDate
ORDER BY d.dt，e.EventID之间
GO
DROP TABLE dbo.Calendar，dbo.EventSchedule;
GO
使用[大师]
GO
DROP DATABASE试验;
GO
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):
Just have a DateOfEvent column that has the date on it.
To grab future events:
Select * from events where DateOfEvent > {DateTime.Now}
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.
Examples:
Every mon, tues, wed, thu, fri, every week
Every wed every week
Every second tuesday of the month
What I used to query
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
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;
You could do something like this:
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);
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.
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屋！
