选择除特定条件的前一天和第二天数据外的所有行

Question

我有一个这样的表

kpl_id     Process_date         baseline_date
----------------------------------------------------
20        2018-04-12 02:00:00         NULL
21        2018-04-13 02:01:00         NULL
22        2018-04-14 02:02:00         NULL
23        2018-04-16 09:15:00         2018-04-17 10:23:00
24        2018-04-16 17:15:00         NULL
25        2018-04-17 02:00:00         NULL
26        2018-04-18 02:00:00         NULL
--------------------------------------------------

我的要求是选择除了baseline_day和它的下一天和前一天以外的所有行。

输出是这样的

kpl_id     Process_date         baseline_date
----------------------------------------------------
20        2018-04-12 02:00:00         NULL
21        2018-04-13 02:01:00         NULL
26        2018-04-18 02:00:00         NULL
--------------------------------------------------

我尝试了下面的查询，我认为这是一个错误的方法。

select Pl.* from process_log_temp PL left outer join
(select *,dateadd(day,-2,baseline_created) d1,dateadd(day,2,baseline_created) d2 from process_log_temp where baseline_created is not null) PL2 on PL2.kpl_id=PL.kpl_id
where  PL.process_date between d1 and d2

当然，它只返回基线日，这是我没有预料到的

这里是所有行

的小提琴环境是SQL Server 2012

Answer 1

首先找到baseline_date，然后从那里得到上一个和下一个日期。然后从表中选择不包含日期范围的

WITH baseline AS
(
SELECT [process_date],
[prev_date] = CONVERT(DATE, DATEADD(DAY, -1, [process_date])),
[next_date] = CONVERT(DATE, DATEADD(DAY, +1, [process_date]))
FROM   [process_log_temp]
WHERE  [baseline_created] IS NOT NULL
)
SELECT *
FROM   process_log_temp p
WHERE  NOT EXISTS
(
SELECT *
FROM   baseline x
WHERE  p.[process_date] > x.[prev_date]
AND    p.[process_date] < DATEADD(DAY, 1, x.[next_date])
)

---

EDIT-1:新的需求

如果基线在22日，如果21日没有数据，则需求为也跳过20日的数据

听起来你想基于行而不是日期

WITH process AS
(
SELECT *, rn = row_number() over (order by [process_date])
FROM   [process_log_temp]    
)
SELECT *
FROM   process p
WHERE  NOT EXISTS
(
SELECT *
FROM   process x
WHERE  x.baseline_created IS NOT NULL
AND    p.rn >= x.rn - 1
AND    p.rn <= x.rn + 1
)

---

EDIT-2:对于同一天的多个条目，使用dense_rank()并将process_date转换为日期进行排序

rn = dense_rank() over (order by convert(date, [process_date]))