65.9K
CodeProject 正在变化。 阅读更多。
Home

如何使用 SQL Server 为预定日期选择记录

Vilhelms
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.29/5 (16投票s)

2009年1月14日

CPOL

8分钟阅读

viewsIcon

84383

downloadIcon

2289

检索约会而无需存储所有未来发生的约会。

引言

我想将约会存储在数据库中，并且能够检索落在特定日期内的记录，而无需将约会的所有发生都存储到某个任意的未来日期。计划是创建描述计划的“主”约会记录，并使用日期逻辑来确定任何给定日期有哪些约会。

此代码支持以下计划

  • 一次性（特定日期）
  • 每日 - 在指定的星期几或每 n 天
  • 每周 - 每 n 周在指定的星期几
  • 每月 – 每 n 月的第 x 天，或每 n 月的第 1/2/3/4/最后一个工作日

为了帮助您更好地了解场景，这里是演示计划程序的屏幕截图。源代码已附上。

scheduler.JPG

Using the Code

在项目源文件夹的 SQL 文件夹中运行 _CreateSchedulerDB.sql_ 脚本以创建 Scheduler 数据库。 Schedule 数据表包含以下字段

  • Startdate (datetime) = 计划开始的日期，即第一次发生（或一次性约会的情况，一次性日期）
  • Enddate (datetime) = 计划结束的日期（如果无限期则为 null）
  • Frequency (tinyint) = 频率类型（1=一次性，2=每日，3=每周，4=每月）
  • Days (tinyint) = 7 位表示星期几，其中周日=1，周一=2，周二=4，周三=8，周四=16，周五=32，周六=64，因此每星期的约会=127。请注意，周日被用作一周的第一天。
  • Interval (smallint) = 间隔的大小
  • IntervalFlag (int) = 用于分支逻辑检查的间隔标志，用于每日或每月有多种选项的情况，例如，每 2 个月的第 1 天，或每 2 个月的第一个星期一。对于像每 3 个月的第一个星期一这样的约会，此字段存储 3。

存储过程 _GetScheduleForDate_ 接受一个日期参数，并返回一个记录集，其中仅包含指定日期内的约会记录。

一次性（频率类型 1）

一次性约会是最容易实现的。选择 _startdate_ 等于检查日期的任何记录。在这种情况下，_EndDate_ 无关紧要，可以忽略。

SELECT * FROM Schedule WHERE StartDate=@CHECKDATE AND Frequency=1
每日（频率类型 2）

每日引入了第一个标量值用户定义函数 _DayValueFromBits_。因为我将所需的日期存储为位掩码，其中周日=1，周一=2 等，此函数可以确定给定日期的星期几，以便与约会记录中的日期进行比较。

CREATE FUNCTION [dbo].[DayValueFromBits]
(
      @CHECKDATE datetime --the date we’re checking
)
RETURNS smallint
AS
BEGIN
    DECLARE @DAY smallint
    SET @DAY = DATEPART(dw,@CHECKDATE)-1
    RETURN POWER(2,@DAY)
END

@DAY_ 存储了检查日期的星期几。因为任何数的零次方都等于 1（即周日），我们必须从日期中减去 1，这样才能得到正确的位。星期六（7）减去 1=6，2 的 6 次方=64。

每日选择就这么简单。 SQL 代码如下

SELECT * FROM Schedule WHERE (((dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 _
	AND IntervalFlag=0) 
Or (datediff(dd,@CHECKDATE,StartDate)%NULLIF(Interval,0) = 0 AND IntervalFlag = 1) ) 
AND StartDate <= @CHECKDATE AND _
(EndDate Is Null OR EndDate > @CHECKDATE) AND Frequency=2

首先，我们调用 _DayValueFromBits_ UDF，并将其与 _Days_ 数据字段进行逻辑 AND 操作。如果结果大于零，则表示日期匹配。

_IntervalFlag_ 在这里用于表示约会是每 n 天（_IntervalFlag_=1）还是每星期几（_IntervalFlag_=0）。 _DayValueFromBits_ 处理星期几。

现在让我们看看每 n 天的情况。在这里，我们使用 _datediff_ 获取开始日期和 _@CHECKDATE_ 之间的天数差，然后对此值进行 MOD 运算（即 _%NULLIF_ 位）以获取余数。如果为零，则表示日期匹配。我们使用 _NULLIF_ 来过滤掉间隔为零的情况，这会导致误报，即，如果间隔匹配为零，则返回 null 值，从而导致模运算失败。

每周（频率类型 3）

在这里，我们使用下一个 UDF _WeeklyDays_。此函数计算开始日期和 _@CHECKDATE_ 之间的周数差，然后将该值与间隔进行 _MODS_ 运算，以确定日期是否有效。没有余数表示匹配成功。 _WeeklyDays_ 函数的 SQL 代码如下所示

CREATE FUNCTION [dbo].[WeeklyDays]
(
    @STARTDATE datetime, --the start date of the appointment
    @CHECKDATE datetime, --the date we’re checking
    @INTERVAL int --the interval
)
RETURNS bit
AS
BEGIN
    DECLARE @WDIFF int, 
    @RESULT bit
    SET @WDIFF = DATEDIFF(ww,@STARTDATE,@CHECKDATE)
    SET @RESULT = 0
    IF @WDIFF%@INTERVAL = 0
        SET @RESULT = 1
    RETURN @RESULT
END

这就是每周选择所需的所有内容。 SQL 查询如下所示

SELECT * FROM Schedule WHERE (dbo.WeeklyDays(StartDate,@CHECKDATE,Interval)=true 
AND (dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 AND StartDate <= @CHECKDATE 
AND (EndDate Is Null OR EndDate > @CHECKDATE)) AND Frequency = 3

_WeeklyDays_ 负责正确的周，而 _DayValueFromBits_ 则像以前一样负责周几。

每月（频率类型 4）

每月使用多个 UDF。月度计算更为复杂，因为我们希望计算一个月内的第 1、2、3、4 或最后一个出现。

下表显示了月度约会的计划字段如何存储，因为它们的值与之前的约会类型不同。最值得注意的是，_Day_ 字段现在存储月份日期或星期几的值，而不是位掩码。

每 n 月的第 n 天
天数 每月第 n 天
Interval 每 n 月的值
IntervalFlag Zero
每 n 月的第 n 个工作日
天数 1=第一次，2=第二次……5=最后一次
Interval 星期几，其中周日=1
IntervalFlag 每 n 月的值

_MonthlyDays_ UDF 执行的功能与 _WeeklyDays_ 类似，但检查月数差异。

如果我们正在检查每 n 月的第 n 天（_IntervalFlag_=0），我们会将月数差加回到开始日期，以确保日期匹配。这可以确保由于月份长度差异导致的月份未滚动。与之前一样，零余数表示匹配成功。

如果我们正在检查每 n 月的第 n 个工作日（_IntervalFlag_=每 n 月的值），则无需将日期加回，但请注意，我们在此处使用 _IntervalFlag_，因为它存储的是 n 月的值，而不是 _Interval_。

CREATE FUNCTION [dbo].[MonthlyDays] 
(
    @STARTDATE datetime,
    @CHECKDATE datetime,
    @INTERVAL int,
    @INTERVALFLAG int
)
RETURNS bit
AS
BEGIN
    DECLARE @MDIFF int,
           @NDIFF datetime,
           @RESULT bit
    SET @RESULT = 0
 
    IF @INTERVALFLAG=0
    BEGIN
        SET @MDIFF = DATEDIFF(mm,@STARTDATE,@CHECKDATE)
        IF @MDIFF%@INTERVAL=0
        BEGIN
            SET @NDIFF = DATEADD(mm,@MDIFF,@STARTDATE)
            IF @NDIFF=@CHECKDATE
                BEGIN
                    SET @RESULT = 1
                END
            END
    END
    ELSE
    BEGIN
        SET @MDIFF = DATEDIFF(mm,@STARTDATE,@CHECKDATE)
        IF @MDIFF%@INTERVALFLAG=0
        BEGIN
            SET @RESULT = 1
        END
    END
    RETURN @RESULT
END

下一个 UDF 是 _MonthlyDayOccurrence_。此函数计算一个月内工作日的第 n 个出现并返回该出现的日期。然后我们可以检查它是否与我们的检查日期匹配。 SQL 代码如下所示

CREATE FUNCTION [dbo].[MonthDayOccurrence]
(
    @CHECKDATE datetime, --the date we’re checking (day is ignored)
    @WEEKDAY int, --the weekday to check for
    @INSTANCE int --the week day instance to find
)
RETURNS datetime
AS
BEGIN
    DECLARE @RESULT datetime
    DECLARE @DAY int
    SET @DAY = DATEPART(d,@CHECKDATE)
    IF @INSTANCE < 5
    BEGIN
        SET @RESULT = @CHECKDATE - @DAY + _
	(7 * @INSTANCE + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
    END
    ELSE
    BEGIN –-last occurrence in the month check
        SET @RESULT = @CHECKDATE - @DAY + _
	(7 * @INSTANCE + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
        IF DATEPART(m,@CHECKDATE) != DATEPART(m,@RESULT)
        BEGIN
            SET @RESULT = @CHECKDATE - @DAY + _
		(7 * 4 + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
        END
    END
    RETURN @RESULT
END

首先，我们使用 _datepart_ 函数从检查日期中获取月份的日期。我们需要将其传递给执行实际工作的以下算法

{Date to check} – {Day of month} + ( 7 * {Instance} + 1 – {Day of week})

例如，让我们说我们想要 2009 年 1 月的第 2 个星期五，其日期恰好是 2009 年 1 月 9 日。使用上面的算法，我们得到（使用 2009 年 1 月 9 日的检查日期）

  • 检查日期 = 2009 年 1 月 9 日
  • 月份的第几天 = 9
  • 出现次数 = 2（即第 2 个星期五）
  • 星期几 = 6（即星期五，基于周日为第 1 天）
  • 2009 年 1 月 9 日 – 9 + (7 * 2 + 1 – 6) =>
  • 2009 年 1 月 9 日 – 9 + 9 =>
  • 09/01/2009

所以我们的检查日期和 _MonthlyDayOccurrence_ 计算出的日期匹配，因此我们知道这是 1 月的第 2 个星期五。

我们还没有完全解决问题，因为如果我们想要最后一个出现（在这种情况下，我们传入出现次数为 5 的实例）怎么办？有时月份有第五个出现，有时则没有，这取决于 1 号是星期几。为了解决这个问题，我们执行与上面相同的检查，然后检查月份是否仍与检查日期匹配。如果不匹配，则表示我们超出了范围，因此我们将第四个出现作为“最后一个”（在这种情况下，第四个和最后一个是相同的）。

最后一个 UDF _WeekDay_ 是标准 Excel 样式 _WeekDay_ 函数的实现，该函数根据提供的星期第一天返回星期几的编号。这由上面讨论的 _MonthlyDayOccurrence_ 算法使用。

CREATE FUNCTION [dbo].[WeekDay]
(
    @DATE datetime,
    @FIRSTDAYOFWEEK int
)
RETURNS int
AS
BEGIN
    DECLARE @OFFSET int,
    @DAY int
    SET @OFFSET = @FIRSTDAYOFWEEK - 1
    SET @DAY = DATEPART(dw,@DATE)
    SET @DAY = @DAY - @OFFSET
    IF @DAY < 1
    BEGIN
        SET @DAY = @DAY + 7
    END
    RETURN @DAY
END

最后一点是，为了使月度约会能够使用上述逻辑工作，当记录保存时，_startdate_ 值需要与约会的第一次出现对齐。因此，有一个 _WeekDay_ 函数和 _MonthlyDayOccurrence_ 过程的客户端实现，在记录保存时会调用它们。这会将 _startdate_ 提前到下一次出现，然后以调整后的日期保存记录。这些位于演示项目中的 _GlobalFunctions static_ 类中。

这就是月度计算所需的一切。 SQL 代码如下所示

SELECT * FROM Schedule WHERE (((dbo.MonthlyDays_
	(StartDate,@CHECKDATE,Interval,IntervalFlag))='true' 
AND datepart(d,@CHECKDATE) = Days AND IntervalFlag = 0) 
Or (@CHECKDATE = dbo.MonthDayOccurrence(@CHECKDATE,Days,Interval) AND 
dbo.MonthlyDays(StartDate,@CHECKDATE,Interval,IntervalFlag)='true' AND 
IntervalFlag > 0)) AND Frequency = 4 AND StartDate <= @CHECKDATE  AND 
(EndDate >= @CHECKDATE OR EndDate = '1900/01/01 00:00:00')

我们再次使用了 _IntervalFlag_ 来表示我们处理的是每 x 月的第 n 天（_IntervalFlag_=0）还是每 n 月的第 1/2 等天（_IntervalFlag_=1）。

现在剩下的是使用 _UNION_ 将每个结果集合并到一个记录集中。

CREATE PROCEDURE [dbo].[GetScheduledAppointments]
(
    @CHECKDATE datetime
)
AS
SET NOCOUNT ON
BEGIN
--one off
    SELECT * FROM Schedule WHERE StartDate=@CHECKDATE AND Frequency=1
UNION
--daily
    SELECT * FROM Schedule WHERE (((dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 
AND IntervalFlag=0) Or (datediff(dd,@CHECKDATE,StartDate)%NULLIF(Interval,0) = 0 
AND IntervalFlag = 1) ) AND StartDate <= @CHECKDATE AND (EndDate Is Null 
OR EndDate > @CHECKDATE) AND Frequency=2
UNION
--weekly
    SELECT * FROM Schedule WHERE (dbo.WeeklyDays(StartDate,@CHECKDATE,Interval)=true 
AND (dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 AND StartDate <= @CHECKDATE 
AND (EndDate Is Null OR EndDate > @CHECKDATE)) AND Frequency = 3
UNION
--monthly
  SELECT * FROM Schedule WHERE (((dbo.MonthlyDays_
	(StartDate,@CHECKDATE,Interval,IntervalFlag))='true' 
AND datepart(d,@CHECKDATE) = Days AND IntervalFlag = 0) 
Or (@CHECKDATE = dbo.MonthDayOccurrence(@CHECKDATE,Days,Interval) 
AND dbo.MonthlyDays(StartDate,@CHECKDATE,Interval,IntervalFlag)='true' 
AND IntervalFlag > 0)) AND Frequency = 4 AND StartDate <= @CHECKDATE  
AND (EndDate >= @CHECKDATE OR EndDate = '1900/01/01 00:00:00')
END

您可以调用 _GetScheduleForDate_ 并传入要检查的日期，该过程将返回指定日期内所有匹配约会的记录集。所有记录选择处理都在服务器端完成，您不会通过网络传输无关的数据记录，SQL 可移植到其他项目，并且您可以在不重新编译程序的情况下调整选择。

注意事项

我住在英国，因此我的客户使用 DD/MM/YYYY 格式的日期。我没有尝试过其他日期格式。

在演示应用程序中，请不要忘记编辑 _app.config_ 文件以根据需要更改数据库实例名称。

未来改进

最好在 SQL Server 端执行日期对齐，以减轻编码人员必须在客户端执行此操作的负担。一旦有解决方案，我将发布更新。

历史

  • 2009 年 1 月 14 日 - 初版文章发布
  • 2009 年 11 月 20 日 - 添加了示例项目和数据库脚本，以及原始版本中遗漏的内容（感谢指出这些问题的人）
© . All rights reserved.