Sql-server – T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

cursorsrecursivesql servert-sql

My sample database (in fact a CTE statement data) looks like this:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  3974
2012-03-28  3965
2012-03-29  3967
2012-03-30  3959
2012-04-02  3951
2012-04-03  3961
2012-04-04  3944
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I want to remove the values that are less than 12 in difference. This is my query and output:

SELECT
eventdate,
CASE
WHEN ABS(val - LAG(val) OVER (ORDER BY eventdate)) <= 12
THEN NULL
ELSE val
END AS val
FROM tbl_1
ORDER BY eventdate

Output:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  NULL
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  NULL
2012-04-03  NULL
2012-04-04  3944
2012-04-05  NULL
2012-04-09  3901
2012-04-10  3822

The problem is that it compares between current and previous row. I need to compare between current and previous non-NULL value, I mean the last value that was not NULLed on the previous step.

This is what I need:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  3965
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  3951
2012-04-03  NULL
2012-04-04  NULL
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I tried the following.
I made a self-referenced CTE query as above replacing THEN NULL with THEN LAG(val) OVER (ORDER BY eventdate) to copy last appropriate value and compare with next. Then remove duplicates. But it deadly loops on OPTION (MAXRECURSION 0) with no results at all.

Seems this could be done by CURSOR only.
I need the result set as CTE statement (within the CTE), to use it in next query.

As far as I looked for CURSOR examples they all are done as a final SELECT to CTE.
This is not an option for me!

I am running SQL Server 2014.

Many thanks for your help or thoughts!

Best Answer

Here is a recursive CTE solution using a technique that Paul White blogged about in Performance Tuning the Whole Query Plan. 

declare @T table
(
  Eventdate date index IX_Eventdate clustered,
  Val int
);

insert into @T(Eventdate, Val) values
('2012-03-23',  3965),
('2012-03-26',  3979),
('2012-03-27',  3974),
('2012-03-28',  3965),
('2012-03-29',  3967),
('2012-03-30',  3959),
('2012-04-02',  3951),
('2012-04-03',  3961),
('2012-04-04',  3944),
('2012-04-05',  3935),
('2012-04-09',  3901),
('2012-04-10',  3822);

with C as
(
  select top(1) 
         T.Eventdate,
         T.Val,
         T.Val as PrevVal1,
         T.Val as PrevVal2
  from @T as T
  order by T.eventdate
  union all
  select T.Eventdate,
         T.Val,
         T.PrevVal1,
         T.PrevVal2
  from (
       select T.Eventdate,
              T.Val,
              iif(abs(T.Val - C.PrevVal1) <= 12, C.PrevVal1, T.Val) as PrevVal1,
              iif(abs(T.Val - C.PrevVal1) <= 12, null, T.Val) as PrevVal2,
              row_number() over(order by T.Eventdate) as rn
       from @T as T
       inner join C
         on C.Eventdate < T.Eventdate
       ) as T
  where T.rn = 1
)
select C.Eventdate,
       C.PrevVal2 as Val
from C
order by C.Eventdate
option (maxrecursion 0);
Related Question