我怎么能存储和查询调度数据? [英] 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屋!