sql - 如何检查SQL中较小的时间段是否完全覆盖了时间段？(How to check if time period is fully covered by smaller periods in SQL?)

Question

Having tables like following:

(具有如下表格：)

create table originalPeriods (
    [Id] INT PRIMARY KEY,
    [Start] DATETIME NOT NULL,
    [End] DATETIME NOT NULL,
    [Flag1] INT NOT NULL,
    [Flag2] INT NOT NULL,
    CONSTRAINT UC_UniueFlags UNIQUE (Flag1,Flag2)
)
go

create table convertedPeriods(
    [Id] INT PRIMARY KEY,
    [Start] DATETIME NOT NULL,
    [End] DATETIME NOT NULL,
    [Flag1] INT NOT NULL,
    [Flag2] INT NOT NULL
)
go

I want to check whether every period from the first table is represented by a set of periods from the second table with matching Flags.

(我想检查第一个表中的每个周期是否由第二个表中具有匹配标志的一组周期表示。)

I want converted periods (from the second table) to fill whole original period (from first period) with no empty spaces, no overlapping and no extensions!

(我希望转换后的期间（从第二个表开始）填充整??个原始期间（从第一个期间开始），没有空格，没有重叠且没有扩展名！)

Converted periods should fit original period exactly.

(转换后的期间应完全适合原始期间。)

The perfect outcome would be list of original periods Id with the flag of whether it is well covered by converted periods.

(理想的结果将是原始期间ID列表，并带有已转换期间是否覆盖良好的标记。)

Thanks in advance for any advises.

(在此先感谢您的任何建议。)

  ask by Rico W translate from so

Answer 1

等待大神答复