sql - 创建记录间隔

Question

我有一个时间表模板表。

CREATE TABLE [dbo].[Template]
(
    [ID] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Interval] [nvarchar](50) NULL,-- reception interval in minutes
    [Activate] [bit] NOT NULL,-- is the template active or not
) ON [PRIMARY]

因此，任何时候只有一个模板处于活动状态，并且每个模板都有第 n 个调度间隔

CREATE TABLE [dbo].[Schedule]
(
    [ID] [uniqueidentifier] NOT NULL,
    [TemplateID] [uniqueidentifier] NULL,
    [StartTime] [nvarchar](50) NULL, --start of available recording time
    [EndTime] [nvarchar](50) NULL,--end of available recording time
    [Monday] [bit] NULL,-- True or false whether the entry is available on Monday
    [Tuesday] [bit] NULL,
    [Wednesday] [bit] NULL,
    [Thursday] [bit] NULL,
    [Friday] [bit] NULL,
    [Saturday] [bit] NULL,
    [Sunday] [bit] NULL
) ON [PRIMARY]

我还有一个表，其中已经存储了记录和占用时间：

CREATE TABLE [dbo].[EventRecord]
(
    [ID] [uniqueidentifier] NOT NULL,
    [PeopleID] [uniqueidentifier] NULL,
    [TemplateID] [uniqueidentifier] NULL,
    [DateTime] [datetime] NULL,-- date and time of an already created entry
    [Interval] [nvarchar](max) NULL,
    [CheckOut] [bit] NULL,
    [Details] [nvarchar](max) NULL
) ON [PRIMARY]

我需要编写一个存储过程（查询），它将返回记录的可用时间（如果可能，在一个查询中）给定：

1. 接收间隔
2. 已被其他记录占用时间
3. 一周中的日子。

我可以建立一个时间表的间隔，但是我如何为一个模板的所有时间表执行此操作并排除我注册的时间。

DECLARE @intervalMinutes int = 10
DECLARE @myDates TABLE
                 (
                     startTime datetime,
                     endTime datetime
                 )

DECLARE @startTime DATETIME = '2016-07-10 08:00'
DECLARE @endTime DATETIME = '2016-07-10 22:00'

;WITH CTE AS
(
    SELECT  @startTime st
    UNION   ALL
    SELECT  DATEADD (MINUTE, @intervalMinutes, st) st
    FROM    cte
    WHERE   DATEADD (MINUTE, @intervalMinutes, st) < @endTime
)
SELECT st, DATEADD (MINUTE, @intervalMinutes, st) 
FROM cte

标签： sqlsql-server