sql-server - 根据频率、工作日和时间表制定下一次报告运行

ID	频率	天	几个月
1	3天	3	-
2	每周	7	-
3	两星期	14	-
4	每月	-	1
5	季刊	-	3

ID	天
1	星期一
2	周二
3	星期三
4	苏氨酸
5	周五

客户ID	开始日期	频率	平日
1	2021-01-11	2（每周）	5（星期五）
2	2021-02-11	2（每周）	1（星期一）
3	2021-03-11	2（每周）	3（星期三）
4	2021-04-11	3（两周）	5（星期五）

这是我遇到过几次的问题。我可以向您展示一种无需任何额外表格即可完成这项工作的方法，但如果这是您需要大量运行的东西，我将在之后讨论可能的性能改进。

我遇到的第一个问题是频率数据。每周、每月、每两周等都在一周中的某一天发生是有意义的。如果频率是“3 天”，我假设您的意思是每 3 天运行一次，并且在这种情况下一周中的哪一天没有意义。对于“3 天”频率，我将选择忽略一周中的哪一天。

下一个问题是我不确定所有数据是否有意义。例如，对于某些客户（例如，客户 ID = 1），开始日期 (2021-01-11) 不属于输入的星期几（星期一）。我假设您希望工作在开始日期后的第一个星期一运行。

我的下一个假设是，当您说下一个运行日期时，您的意思是下一个日期在开始日期或之后以及今天或之后。

有了这些假设/约束，我们就可以开始一个解决方案。第一件事是我需要提供数字，所以我将使用众所周知的技术创建它们（请参阅“Pro SQL Server 关系数据库设计和实现”中的第 12 章进行讨论，或查看本书的代码在他们的 git repo https://github.com/Apress/pro-sql-server-rel-db-design-impl/blob/master/Code/Chapter%2012.sql中，或者您可以找到其他来源）。

因此，让我们创建一个公用表表达式 (CTE) 来创建数字 1 - 9，然后使用这些数字生成数字 0 - 10,000。我选择了 10,000 数字，因为这样可以使用超过 25 年的日期（25 年大约是 9,132 天）。

使用这些数字，我们可以将它们添加到开始日期。我们需要添加天数或月数，因此我们可以使用 aCASE来进行正确的添加。此外，当我们进行添加时，它可能不在正确的星期几，因此我们会找到添加后的第一个日期在正确的星期几（“3-Day”频率除外）我上面提到过）。

有几件事让这看起来很难看 - 例如，Weekdays 表中的示例数据使用 3 个字母的缩写与 SQL Server 返回的工作日名称不匹配，因此我们必须考虑这种不匹配。

让我们用你的表创建一个脚本然后开始（注意：我在 Schedule 表中添加了更多项目）：

DECLARE @Frequency AS TABLE(
    Id INT NOT NULL, 
    Frequency VARCHAR(15) NOT NULL, 
    [Days] INT NULL, 
    Months INT NULL
);

INSERT INTO @Frequency(Id,Frequency,[Days],Months)
VALUES
(1, '3-Day', 3, NULL), (2, 'Weekly', 7, NULL), 
(3, 'Fortnight', 14, NULL), (4, 'Monthly', NULL, 1), 
(5, 'Quarterly', NULL, 3);

DECLARE @Weekdays AS TABLE(
    Id INT NOT NULL, 
    [Day] CHAR(3) NOT NULL
);
INSERT INTO @Weekdays(Id,[Day])
VALUES
(1, 'Mon'), (2, 'Tue'), (3, 'Wed'), 
(4, 'Thr'), (5, 'Fri');

DECLARE @Schedule AS TABLE( 
    CustomerId INT NOT NULL, 
    StartDate DATE NOT NULL, 
    FrequencyId INT NOT NULL, 
    WeekDayId INT NOT NULL
);
INSERT INTO @Schedule(CustomerId,StartDate,FrequencyId,WeekDayId
)
VALUES
(1, '2021-01-11', 2, 5), (2, '2021-02-11', 2, 1), 
(3, '2021-03-11', 2, 3), (4, '2021-04-11', 3, 5), 
(5, '2021-01-01', 4, 3), (6, '2021-08-01', 1, 2);

-- create the digits 0 - 9
;WITH digits (I) AS (
    SELECT I FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) 
    AS digits(I)
), 
-- create numbers 0 - 10,000
numbers (I) AS (
    SELECT D1.I + (D2.I * 10) + (D3.I * 100) + (D4.I * 1000) AS I
    FROM digits AS D1 CROSS JOIN digits AS D2 CROSS JOIN digits AS D3 CROSS JOIN digits AS D4
), 
-- join the sample data to get the schedule info
schedule AS (
    SELECT  sched.CustomerId,
            sched.StartDate,
            d.[Day],
            freq.Frequency,
            freq.[Days],
            freq.Months, 
            (
                -- find the next date to run... add the correct number of units
                SELECT TOP (1) CASE WHEN freq.Months IS NOT NULL THEN DATEADD(MONTH, numbers.I, sched.StartDate) 
                ELSE DATEADD(DAY, numbers.I, sched.StartDate) END
                FROM numbers 
                WHERE 
                    -- get the date that is the number of months or days ahead and after today
                    CASE WHEN freq.Months IS NOT NULL THEN DATEADD(MONTH, numbers.I, sched.StartDate) 
                    ELSE DATEADD(DAY, numbers.I, sched.StartDate) END >= GETDATE() 
                    AND 
                    -- ignore the weekday for 3-Day frequency with a CASE nested in the JOIN
                    CASE WHEN freq.Frequency <> '3-Day' THEN 
                        -- change the custom abbreviations to what SQL Server needs
                        CASE WHEN d.[Day] = 'Mon' THEN 'Monday' WHEN d.[Day] = 'Tue' THEN 'Tuesday' 
                        WHEN d.[Day] = 'Wed' THEN 'Wednesday' WHEN d.[Day] = 'Thr' THEN 'Thursday' ELSE 'Friday' 
                    END 
                    ELSE '1'
                    END = 
                    CASE WHEN freq.Frequency <> '3-Day' THEN 
                        DATENAME(WEEKDAY, 
                            CASE WHEN freq.Months IS NOT NULL THEN DATEADD(MONTH, numbers.I, sched.StartDate) 
                                ELSE 
                                DATEADD(DAY, numbers.I, sched.StartDate) 
                            END ) 
                    ELSE '1' 
                    END 
                    AND 
                    -- deal with multiples of the correct interval for 3-Day
                    CASE WHEN freq.Frequency ='3-Day' THEN numbers.I % COALESCE(freq.[Days], freq.[Months]) ELSE 1 END = 
                    CASE WHEN freq.Frequency ='3-Day' THEN 0 ELSE 1 END
                ORDER BY numbers.I
            ) AS NextRun
    FROM @Schedule AS sched
    INNER JOIN @Weekdays AS d ON sched.WeekDayId = d.Id 
    INNER JOIN @Frequency AS freq ON sched.FrequencyId = freq.Id 
)
SELECT schedule.CustomerId,
       schedule.StartDate,
       schedule.[Day],
       schedule.Frequency,
       schedule.[Days],
       schedule.Months,
       schedule.NextRun 
FROM schedule 
ORDER BY schedule.CustomerId;

当我在 2021 年 8 月 10 日运行它时，我得到了这些似乎正确的结果：

客户ID	开始日期	天	频率	天	几个月	下一个运行
1	2021-01-11	周五	每周	7	无效的	2021-08-13
2	2021-02-11	星期一	每周	7	无效的	2021-08-16
3	2021-03-11	星期三	每周	7	无效的	2021-08-11
4	2021-04-11	周五	两星期	14	无效的	2021-08-13
5	2021-01-01	星期三	月刊	无效的	1	2021-09-01
6	2021-08-01	周二	3天	3	无效的	2021-08-13

如果您需要经常这样做，您可以在数据库中创建一个数字表，然后在您发现性能不足时使用该表而不是 CTE。请参阅我之前引用的书或 git 存储库，以获取制作表格的简单脚本。对于这个小数据集，脚本运行时间为 270 毫秒，这相当不错。

如果我的假设不正确，您可以调整任何需要调整的逻辑。我希望这有助于并回答您的问题。

sql-server - 根据频率、工作日和时间表制定下一次报告运行

问题描述

解决方案

推荐阅读