删除/忽略具有不重叠编号规则(不是日期序列)的条目

Question

我有一些条目，可以根据周期类型和从/到值形成序列。

周期类型可以是"W"(周可能的范围 1-53(、"M"(第 1-12 个月(和一些特殊类型，但在一年内，并且始终具有 1..x 的范围

一个条目可以覆盖一个(完整(范围，也可以有多个条目覆盖该范围(有或没有间隙，不一定是完整的范围(。此外，序列可以在允许范围内的某个地方开始。

所以有效将是：

W    1   12
W   13   25
W   26   52

无效将是

W    1   12
W   11   53 <-- overlap

现在我需要一种方法来过滤/忽略有效的，只显示无效的。不幸的是，它必须在SQL中，因为它适用于不支持任何脚本的报告工具。

在小提琴 http://www.sqlfiddle.com/#!18/f2c32/1 中，带有 W 1-53 的条目是无效的条目。

(带有"NULL"的结果行仅适用于视觉间距(

Answer 1

下面是一个使用 LEAD 窗口函数获取下一个范围并将当前范围与下一个范围进行比较的版本。

SQL 小提琴

MS SQL Server 2017 架构设置：

CREATE TABLE [dbo].[Test](
    [customer] [nchar](10) NULL,
    [key1] [nchar](10) NULL,
    [key2] [nchar](10) NULL,
    [key3] [nchar](10) NULL,
    [period] [nchar](1) NULL,
    [period_from] [int] NULL,
    [period_to] [int] NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Test]([customer], [key1], [key2], [key3], [period], [period_from], [period_to])
VALUES 
(N'A         ', N'XYZ       ', N'123       ', N'000       ', N'W', 1, 53), 
(N'A         ', N'XYZ       ', N'123       ', N'111       ', N'W', 1, 53),
(N'A         ', N'ABC       ', N'123       ', N'000       ', N'Q', 1, 2), 
(N'A         ', N'ABC       ', N'123       ', N'333       ', N'Q', 3, 3), 
(N'A         ', N'YYY       ', N'321       ', N'CCC       ', N'W', 1, 20),
(N'A         ', N'YYY       ', N'321       ', N'DDD       ', N'W', 21, 30),
(N'A         ', N'YYY       ', N'321       ', N'EEE       ', N'W', 31, 53),
(N'B         ', N'XYZ       ', N'123       ', N'000       ', N'W', 1, 53), 
(N'B         ', N'XYZ       ', N'123       ', N'111       ', N'W', 1, 53),
(N'B         ', N'ABC       ', N'123       ', N'000       ', N'Q', 1, 2), 
(N'B         ', N'ABC       ', N'123       ', N'333       ', N'Q', 3, 3), 
(N'B         ', N'YYY       ', N'321       ', N'CCC       ', N'W', 1, 20),
(N'B         ', N'YYY       ', N'321       ', N'DDD       ', N'W', 21, 30),
(N'B         ', N'YYY       ', N'321       ', N'EEE       ', N'W', 31, 53)

查询 1：

    -- This solution assumes that the unique keys are (customer, key1, key2 and period)
    -- Adjust based on your needs
    ;WITH WithNext AS (select 
      *, 
      LEAD(period_from) OVER(PARTITION BY customer, key1, key2, period ORDER BY period_from, period_to) AS next_period_from,
      LEAD(period_to) OVER(PARTITION BY customer, key1, key2, period ORDER BY period_from, period_to) AS next_period_to
      from Test
    ) 
    SELECT * FROM Test t
     WHERE NOT EXISTS (
       SELECT * FROM WithNext w 
       WHERE 
         w.customer = t.customer 
         AND w.key1 = t.key1
         AND w.key2 = t.key2
         AND w.period = t.period
         AND (w.next_period_from BETWEEN w.period_from AND w.period_to)
      )
    ORDER BY customer, key1, key2, period

结果：

|   customer |       key1 |       key2 |       key3 | period | period_from | period_to |
|------------|------------|------------|------------|--------|-------------|-----------|
| A          | ABC        | 123        | 000        |      Q |           1 |         2 |
| A          | ABC        | 123        | 333        |      Q |           3 |         3 |
| A          | YYY        | 321        | CCC        |      W |           1 |        20 |
| A          | YYY        | 321        | DDD        |      W |          21 |        30 |
| A          | YYY        | 321        | EEE        |      W |          31 |        53 |
| B          | ABC        | 123        | 000        |      Q |           1 |         2 |
| B          | ABC        | 123        | 333        |      Q |           3 |         3 |
| B          | YYY        | 321        | CCC        |      W |           1 |        20 |
| B          | YYY        | 321        | DDD        |      W |          21 |        30 |
| B          | YYY        | 321        | EEE        |      W |          31 |        53 |