sql-server - SQL : generate schedule table with different frequencies

Question

我正在 SQL Server 2012 中工作。我有 3 个表。第一个是“时间表”表。其结构如下:

CREATE TABLE schedule
(
    JobID int
    ,BeginDate date
    ,EndDate date
)

一些示例数据是:

INSERT INTO schedule
SELECT 1, '2017-01-01', '2017-07-31' UNION ALL
SELECT 2, '2017-02-01', '2017-06-30'

第二个是“频率”表。其结构如下:

CREATE TABLE frequency
(
    JobID int
    ,RunDay varchar(9)
)

一些示例数据是:

INSERT INTO frequency
SELECT 1, 'Sunday' UNION ALL
SELECT 1, 'Monday' UNION ALL
SELECT 1, 'Tuesday' UNION ALL
SELECT 1, 'Wednesday' UNION ALL
SELECT 1, 'Thursday' UNION ALL
SELECT 1, 'Friday' UNION ALL
SELECT 1, 'Saturday' UNION ALL
SELECT 2, 'Wednesday'

第三个是“日历”表。其结构如下:

CREATE TABLE calendar
(
    CalendarFullDate date
    ,DayName varchar(9)
)

我的目标是“取消透视”计划表，以便为每个 JobID 的跨越 BeginDate 和 EndDate 日期范围的每个日期创建一行。这些行必须与每个 JobID 的频率表中的天数匹配。

到目前为止，每项工作的日期频率都是每天或每周。为此，我使用以下 SQL 来生成我想要的表:

SELECT
    s.JobID
    ,c.CalendarFullDate
FROM
    schedule AS s
INNER JOIN
    calendar AS c
ON
    c.CalendarFullDate BETWEEN s.BeginDate AND s.EndDate
INNER JOIN
    frequency AS f
ON
    f.JobID = s.JobID
    AND f.RunDay = c.DayName

这不适用于高于每周(例如，每两周)的频率。为此，我知道我的频率表需要更改结构。特别是，我必须添加一列给出频率(例如，每天、每周、每两周)。而且，我打赌我还需要在日历表中添加周数列。

如何生成所需的表格以容纳至少每两周一次的频率(如果不是更高的频率)？例如，如果 JobID = 3 是一个在星期三运行的双周作业，并且它受 BeginDate = '2017-06-01' 和 EndDate = '2017-07-31' 的约束，那么对于此作业，我会预期结果如下:

JobID    Date
3        2017-06-07
3        2017-06-21
3        2017-07-05
3        2017-07-19

Answer 1

我更改了时间表而不是频率表。我添加了一个 SkipWeeks 字段，该字段应设置为 1 表示每两周一次，设置为 2 表示每三周运行一次作业，等等。我使用了表值函数来返回正确的日期。我想这就是你想要的。

CREATE TABLE dbo.schedule
(
    JobID int
    ,BeginDate date
    ,EndDate date
    ,SkipWeeks tinyint default 0
)
GO
INSERT INTO dbo.schedule
(JobID,BeginDate,EndDate)
SELECT 1, '2017-01-01', '2017-07-31' UNION ALL
SELECT 2, '2017-02-01', '2017-06-30'
GO
CREATE TABLE dbo.frequency
(
    JobID int
    ,RunDay varchar(9),
    primary key (
        JobID,
        RunDay
    )
)
GO
INSERT INTO dbo.frequency
SELECT 1, 'Sunday' UNION ALL
SELECT 1, 'Monday' UNION ALL
SELECT 1, 'Tuesday' UNION ALL
SELECT 1, 'Wednesday' UNION ALL
SELECT 1, 'Thursday' UNION ALL
SELECT 1, 'Friday' UNION ALL
SELECT 1, 'Saturday' UNION ALL
SELECT 2, 'Wednesday'
GO
CREATE FUNCTION dbo.DateRangeTable(@pdStartDate date, @pdEndDate date, @piSkipWeeks tinyint)
RETURNS 
@dates TABLE 
(
    [Date] date primary key,
    DayName varchar(9)
)
AS
BEGIN
    declare @ldDate date = @pdStartDate
    declare @skipDecr smallint = @piSkipWeeks
    while (@ldDate <= @pdEndDate) Begin
        if @skipDecr = 0 Begin
            insert into @dates
            select @ldDate, format(@ldDate,'dddd')
        End
        --Start of New week? (% = MOD)
        if datediff(d,@pdStartDate,@ldDate) % 7 = 0 Begin 
            if @skipDecr = 0 Begin
                set @skipDecr = @piSkipWeeks
            End else Begin
                set @skipDecr = @skipDecr - 1
            End
        End
        set @ldDate = dateadd(D,1, @ldDate)
    End

    RETURN 
END
GO
INSERT INTO dbo.schedule
(JobID,BeginDate,EndDate,SkipWeeks)
SELECT 3, '2017-06-01','2017-07-31',1 
GO
INSERT INTO dbo.frequency
SELECT 3, 'Wednesday'
GO
SELECT
    s.JobID
    ,c.[Date]
FROM dbo.schedule AS s
cross apply dbo.DateRangeTable(s.BeginDate, s.EndDate, s.SkipWeeks) c
INNER JOIN dbo.frequency AS f
ON f.JobID = s.JobID
    AND f.RunDay = c.DayName
GO