SQL: generate schedule table with different frequencies

user_3100444 0
Viewed 1521

I am working in SQL Server 2012. I have 3 tables. The first is a "schedule" table. Its structure is like:

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

Some sample data is:

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

The second is a "frequency" table. Its structure is like:

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

Some sample data is:

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'

The third is a "calendar" table. Its structure is like:

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

My goal is to "unpivot" the schedule table so that I create a row for each date spanning the date range in BeginDate and EndDate for each JobID. The rows must match the days in the frequency table per JobID.

Up until now, the frequencies of dates for each job are either daily or weekly. For this, I use the following SQL to generate my desired table:

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

This doesn't work for frequencies that are higher than weekly (e.g., bi-weekly). To do so, I know that my frequency table would need to change structure. In particular, I would have to add a column that gives the frequency (e.g., daily, weekly, bi-weekly). And, I'm betting that I will need to add a week number column to the calendar table as well.

How can I generate my desired table to accommodate at least bi-weekly frequencies (if not higher frequencies)? For example, if JobID = 3 is a bi-weekly job that runs on Wednesday, and it's bound by BeginDate = '2017-06-01' and EndDate = '2017-07-31', then, for this job, I would expect the following in the result:

JobID    Date
3        2017-06-07
3        2017-06-21
3        2017-07-05
3        2017-07-19
sql-server
1 Answers

I have changed the schedule table instead of the frequency table. I have added a SkipWeeks field that should be set to 1 for bi-weekly, 2 to run the job every third week etc. I have used a table-valued function to return the right dates. I think this is what you wanted.

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
user_99767840
Related