Select all rows expect previous and next day data of a specific condition-CodePudding

I have got a table like this

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

My requirement is to select all rows except baseline_day and its next and previous days.

So output looks like this

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

I tried below query which I believe its a wrong approach.

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

and of course it returned only baselines day which I was not expecting

Here is the fiddle with all rows

Environment is SQL Server 2012

CodePudding user response：

First you find the baseline_date and then from there get the previous and next date. After that just select from the table excluding that date range

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: New requirement

if baseline on 22 and if there is no data in 21st, then requirement is to skip data of 20th as well

Sounds like you want to based on row rather than date

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 : for multiple entry of same day, use dense_rank() and convert the process_date to date for ordering

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